Using ROW_NUMBER

ROW_NUMBER() OVER (PARTITION BY ORDER BY ) AS RowNum

Deleting a user from all db's

If you need to recursively remove a user from all db's, run this.
What happens is the statement first checks whether the user is in the 'current' database (remember msforeachdb)
then, changes the current context to the database, executes the dropuser command, then prints that it did it.

DECLARE @usr sysname
DECLARE @passwd varchar(20)
SET @usr = 'AUser'
SET @passwd = 'password'


DECLARE @cmd1 nvarchar(2000)
SET @cmd1 = 'if exists (select name from ?.dbo.sysusers where upper(name) = '''+@usr+''') '+
'begin '+
'use ? '+
'exec sp_dropuser ''' +@usr+ ''''+
'print ''dropped user from ?'''+
'end'
exec sp_msforeachdb @cmd1

Using a CSV array as a filter in a Stored Procedure

Here's a really handy trick I picked up.
If you have a stored procedure and you would like to pass a set of values in as part of a filter, ie. A list of Identifiers, or Names that you want to filter on, then you can use a handy table that contains just a bunch of numbers. I'll show you.

-- Create the table
CREATE TABLE [dbo].[Nums](
[n] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[n] ASC
) ON [PRIMARY]
) ON [PRIMARY]

-- Now add the numbers
DECLARE @index int
SET @index = 1
WHILE (@index < @NumbersToAdd)
BEGIN
INSERT INTO [dbo].[Nums] (n)
VALUES (@index)
SET @index = @index +1
END

create a function to put the values into a table.

CREATE FUNCTION dbo.ufn_CommaValues(@List varchar(8000))
RETURNS @Array Table
(
pos integer,
val varchar(10)
)
AS
BEGIN
INSERT @Array
SELECT
n - LEN(REPLACE(LEFT(@arr, n), ',', '')) + 1 AS pos,
CAST(SUBSTRING(@arr, n, CHARINDEX(',', @arr + ',', n) - n) AS INT) AS val
FROM Nums --Uses the Nums table.
WHERE n <= LEN(@arr)
AND SUBSTRING(',' + @arr, n, 1) = ','
ORDER BY pos
RETURN
END

Now we have everything you can use it like this:

CREATE PROC dbo.usp_Example
@List varchar(8000) --This is a list of comma separated GUIDS
AS
SELECT *
FROM TableA
INNER JOIN dbo.ufn_CommaValues(@List) CSV ON TableA.Id = CSV.Val


Pretty useful if you have a list of records that you want to filter on, particularly for debugging.

Using a CSV/TXT as a Table

Found a great way to use a csv file as a table in SQL

OpenRowset(
'MSDASQL'
, 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=;'
, 'select * from []'
)


You can load this into a table or use it directly. Just ensure the first line in the csv/txt is the name of the columns then you can also use them in SQL.

Very handy

Moving tempdb

To move the data files for tempdb use the following:


USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', FILENAME = 'path_and_filename.mdf')
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', FILENAME = 'path_and_filename.ldf >')
GO