More T-SQL

- In T-SQL, string comparison is NOT case-sensitive.
  IF 'asc' = 'ASC' PRINT 'equal'
  ELSE PRINT 'NOT equal'
  -- This will out put 'equal'.

- Construct dynamic query and run it in stored procedure. Example:
 
  DECLARE @cmd varchar(200)
  DECLARE @cond varchar(100) = ' WHERE name=''Mary'''
  SET @cmd = 'SELECT * from Users ' + @cond 
  EXEC (@cmd)

- Add a row number in the returned data set:

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users

  Note that if "ORDER BY" is used at the end of the query, it should match the
  "ORDER BY" in the ROW_NUMBER() to be fast and starts from 1. E.g.:

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users ORDER BY UserID DESC

  But the following will run slow, and does not start from 1 (actually list the last 10 rows of the returned list):

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users ORDER BY UserID ASC

- Create a unique value of type uniqueidentifier.
  See: http://msdn.microsoft.com/en-us/library/ms190348.aspx

  -- Creating a local variable with DECLARE/SET syntax.
  DECLARE @myid uniqueidentifier
  SET @myid = NEWID()
  PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)

- Split a string by delimiter ','.
  Reference: http://codebetter.com/raymondlewallen/2005/10/26/quick-t-sql-to-parse-a-delimited-string/    

  DECLARE @loadTable varchar(100) = 'a,b,c,d'
  DECLARE @pos int
  DECLARE @tblResult TABLE (v varchar(10))
  DECLARE @piece varchar(10)

  -- Need to tack a delimiter onto the end of the input string if one doesn’t exist
  IF right(rtrim(@loadTable),1) <> ',' SET @loadTable = @loadTable  + ','

  SET @pos =  patindex('%,%' , @loadTable)
  WHILE @pos <> 0
  BEGIN
    SET @piece = left(@loadTable, @pos - 1)
    INSERT INTO @tblResult (v) VALUES ( @piece ) -- Add to the batch list.
    SET @loadTable = stuff(@loadTable, 1, @pos, '')
    SET @pos =  patindex('%,%' , @loadTable)
  END
    
  select * from @tblResult 

- Temporary table and in-memory table
  1) Temporary table:
  A temporary table is stored in "System Databases\tempdb\Temporary Tables\".

  To declare a table as a local temporary table, use "#" in front of it.
  To declare a table as a global temporary table, use "##" in front of it.
  See Quick Overview: Temporary Tables in SQL Server 2005.

  CREATE TABLE #MyTable1 ( ID int NOT NULL, name varchar(100) )
  INSERT INTO #MyTable1 (ID, name) VALUES (1, 'Mike')
  CREATE INDEX ix_MyTable1ID ON #MyTable1 (ID)
  SELECT * FROM #MyTable1
  DROP TABLE #MyTable1

  The table will be stored as something like "System Databases\tempdb\Temporary Tables\dbo.#MyTable1____________________________________00000002FBEE".

  The table #MyTable1 stays there after the query is done, unless you call the DROP statement.

  Note that if you don't use the "#", then MyTable1 will be created as a physical table in current database.

  2) In comparison, an in-memory table (table variable) can be used this way:

  DECLARE @x TABLE (ID int NOT null, name varchar(100))
  INSERT INTO @x (ID, name) VALUES ('1', 'Mike')
  SELECT * FROM @x

  Note for this one, you can't use index or DROP statement. The table x disappears after the query is done.

- Use cursor
  DECLARE @ID int
  DECLARE @ct int
  DECLARE cs CURSOR FOR SELECT ID FROM table
  OPEN cs

  FETCH NEXT FROM cs INTO @ID
  WHILE @@FETCH_STATUS = 0
  BEGIN
    @ct = @ct + 1 -- keep count of cycles
    PRINT 'cycle: ' + CONVERT(varchar(20), @int)

    FETCH NEXT FROM cs INTO @ID
  END

  CLOSE cs
  DEALLOCATE cs

- ERROR_MESSAGE() - returns error message when @@error != 0

- exception handling in SQL. E.g.:
  BEGIN TRY
    ...
  END TRY

  BEGIN CATCH
    if @@ERROR <> 0 print ERROR_MESSAGE()
  END CATCH

- OUTPUT
  This returns output of a query. The grammar is a little convoluted though. E.g.:

  Declare @sql as nvarchar(512) 
  Declare @params nvarchar(512) = N'@outParam int OUTPUT'
  Declare @out int
  set @sql = 'select @outParam = count(*) from users'
  execute sp_executesql @sql, @params, @outParam = @out OUTPUT   
  print 'out = ' + CONVERT(varchar(100), @out) -- result is in @out

- Get count of rows in each table:
  DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
  INSERT INTO @TableRowCounts ([TableName], [RowCount])
  EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
  SELECT [TableName], [RowCount]
  FROM @TableRowCounts
  ORDER BY [TableName]

- Show space usage:
  DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128),
                              [Rows] VARCHAR(128),
                              [Reserved] VARCHAR(128),
                              [Data] VARCHAR(128),
                              [Index] VARCHAR(128),
                              [Unused] VARCHAR(128)) ;
  INSERT INTO @TableRowCounts ([TableName], [Rows], [Reserved], [Data], [Index], [Unused])
  EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''';
  SELECT [TableName], [Rows], [Reserved], [Data], [Index], [Unused]
  FROM @TableRowCounts
  ORDER BY [TableName]

- A Visual Explanation of SQL Joins

- Run query on remote linked server, e.g., return a list of databases.
  SELECT * FROM openquery([linked server], 'select name from sys.databases');