"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

September 03, 2011

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 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!!!

No comments: