Revising TSQL Enhancements in SQL 2005, SQL 2008

If you are looking for SQL 2011 - Denali TSQL enhancements, Please check blogpost in brentozar site Leaving the Windows Open.

I will keep this post as my reference to learn Denali TSQL Improvements. I wanted to summarize TSQL enhancements for SQL 2005/2008. This post is for that purpose

SQL 2005 TSQL Enhancements

• SQL 2005 - TSQL Enhancements - This post covers CTE, ROW_NUMBER( ), CTE, Pivot, rank( ), ntile( ), dense_rank( )
• Example use of feature - CTE, ROW_NUMBER( ), OUTPUT - Basics - Deleting Duplicate Entries using ROW_NUMBER() Function
• T-SQL Set Operators INTERSECT, EXCEPT, and UNION
• Using NTILE with Cross Reference Values to Create Dimensions

SQL 2008 TSQL Enhancements

This is based on TSQL 2008 enhancement session that I took couple of years back for one of my previous employer.

• New Date/Time Data Types were Introduced - DATE, TIME, DATETIME2, DATETIMEOFFSET 
• Declare and Initialize variables feature. Declare and initialize in same statement
• New Assignment Operators - +=, -=, *=, /=, %=. Example
   
• DECLARE @a AS INT =5, @b AS INT =5, @C AS INT = 5
  SET @b+=5
  SET @a+= @b
  SET @C*=5
  PRINT @a
  PRINT @b
  PRINT @C
  
  
  
  
• ROW CONSTRUCTORS - Simplify Insert Operations. Insert multiple records in a single statement
   
• Table Value Parameter - Pass Array of Elements. Eliminates Parse, SQL Injections. Please find below example on using TVP. Example  

• --Step 1

  --Diff Between Table Type and Table variable

  DROP TABLE TEST;

  DROP PROCEDURE AddRecords;

  DROP TYPE TvpTest;
  
  

  --Step 2

  CREATE TABLE TEST (Id int not null, Name varchar(100))
  

  GO

  CREATE TYPE TvpTest AS TABLE (Id int not null, Name varchar(100))

  GO
  
  
  
  --Step 3

  --2, READ ONLY OPTION MANDATORY
  CREATE PROCEDURE AddRecords (@TableVariable TvpTest READONLY)
  

  AS

  BEGIN

  INSERT INTO TEST

  SELECT * FROM @TableVariable

  END

  GO
  
  
  --Step 4, ROW CONSTRUCTORS

  DECLARE @TTTABLE AS TvpTest
  

  INSERT INTO @TTTABLE VALUES

  (1,'Sijo'),

  (2,'Sivaram'),

  (3,'Adam')

  EXEC AddRecords @TableVariable = @TTTABLE

  SELECT * FROM TEST
  
• Merge Feature  - Conditionally insert, update and delete data
   
• --Step 1
  IF OBJECT_ID('Table1') IS NOT NULL
      DROP TABLE Table1;
  CREATE TABLE Table1 (
      Id   INT       PRIMARY KEY CLUSTERED,
      Name CHAR (50)
  );
  GO
  DROP TYPE TVPMerge;
  
  
  

  
  

  --Step 2, Row Constructors
  INSERT  INTO Table1
  VALUES (1, 'Sijo'),
  (2, 'Sivaram'),
  (3, 'Adam'),
  (4, 'Mike');
  
  
  
  --Step 3
  CREATE TYPE TVPMerge  AS TABLE (
      Id   INT      ,
      Name CHAR (50));
  GO
  
  
  
  --Step 4
  DECLARE @TVPTABLE AS TVPMerge;
  INSERT  INTO @TVPTABLE
  VALUES (1, 'Sijo LastName'),
  (2, 'Sivaram LastName1'),
  (3, 'Adam LastName2'),
  (100, 'New Joinee');
  MERGE INTO Table1
   AS A
  USING @TVPTABLE AS B ON A.Id = B.Id
  WHEN MATCHED THEN UPDATE
  SET A.Name = B.Name
  WHEN NOT MATCHED THEN INSERT (Id, Name) VALUES (B.Id, B.Name)
  WHEN NOT MATCHED BY SOURCE THEN DELETE;
  GO
  SELECT *
  FROM   Table1;
  
• Spatial Data - Geometry and Geography Data Types
• Grouping Sets, ROLLUP and CUBE( ), GROUPING SETS in SQL Server 2008, Grouping Sets in SQL Server 2008 - Example II
  
• CREATE TABLE TABLEA (COL1 INT, COL2 VARCHAR(10), COL3 INT)
  GO
  INSERT INTO TABLEA VALUES (1, 'A', 10), (1, 'A', 20),(1, 'A', 30),(1, 'B', 90),(2, 'A', 30),(2, 'A', 100),(3, 'C', 110),(3, 'C', 120)
  GO
  
  
  --GROUP BY DIFFERENT OPTIONS
  SELECT COL1, COL2, COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUE
  FROM TABLEA
  GROUP BY GROUPING SETS
  (
  (COL1, COL2),
  (COL2),
  (COL1)
  )
  ORDER BY 1
  GO
  
  
  --EQUIVALENT OF ABOVE GROUPING SETS USING TSQL
  SELECT COL1,  NULL AS 'COL2', COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUE
  FROM TABLEA
  GROUP BY COL1
  UNION
  SELECT NULL AS 'COL1', COL2, COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUE
  FROM TABLEA
  GROUP BY COL2
  UNION
  SELECT COL1, COL2, COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUE
  FROM TABLEA
  GROUP BY COL1, COL2
  GO
• FileStream Support for Storing Files, HierarchyId - Storing Hierarchical / Tree Structure
• Filtered Indexes - Nonclustered index which can be created only on certain set of records, Subset of records

Happy Learning!!!