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 = 5SET @b+=5SET @a+= @bSET @C*=5PRINT @aPRINT @bPRINT @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 variableDROP TABLE TEST;DROP PROCEDURE AddRecords;DROP TYPE TvpTest;
--Step 2CREATE TABLE TEST (Id int not null, Name varchar(100))
GOCREATE TYPE TvpTest AS TABLE (Id int not null, Name varchar(100))GO--2, READ ONLY OPTION MANDATORY
--Step 3
CREATE PROCEDURE AddRecords (@TableVariable TvpTest READONLY)
ASBEGININSERT INTO TESTSELECT * FROM @TableVariableENDGODECLARE @TTTABLE AS TvpTest
--Step 4, ROW CONSTRUCTORS
INSERT INTO @TTTABLE VALUES(1,'Sijo'),(2,'Sivaram'),(3,'Adam')EXEC AddRecords @TableVariable = @TTTABLESELECT * FROM TEST
- Merge Feature - Conditionally insert, update and delete data
- --Step 1IF OBJECT_ID('Table1') IS NOT NULLDROP TABLE Table1;CREATE TABLE Table1 (Id INT PRIMARY KEY CLUSTERED,Name CHAR (50));GODROP TYPE TVPMerge;
--Step 2, Row ConstructorsINSERT INTO Table1VALUES (1, 'Sijo'),(2, 'Sivaram'),(3, 'Adam'),(4, 'Mike');
--Step 3CREATE TYPE TVPMerge AS TABLE (Id INT ,Name CHAR (50));GO
--Step 4DECLARE @TVPTABLE AS TVPMerge;INSERT INTO @TVPTABLEVALUES (1, 'Sijo LastName'),(2, 'Sivaram LastName1'),(3, 'Adam LastName2'),(100, 'New Joinee');
MERGE INTO Table1AS AUSING @TVPTABLE AS B ON A.Id = B.IdWHEN MATCHED THEN UPDATESET A.Name = B.NameWHEN NOT MATCHED THEN INSERT (Id, Name) VALUES (B.Id, B.Name)WHEN NOT MATCHED BY SOURCE THEN DELETE;GOSELECT *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)GOINSERT 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 OPTIONSSELECT COL1, COL2, COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUEFROM TABLEAGROUP BY GROUPING SETS((COL1, COL2),(COL2),(COL1))ORDER BY 1GO
--EQUIVALENT OF ABOVE GROUPING SETS USING TSQLSELECT COL1, NULL AS 'COL2', COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUEFROM TABLEAGROUP BY COL1UNIONSELECT NULL AS 'COL1', COL2, COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUEFROM TABLEAGROUP BY COL2UNIONSELECT COL1, COL2, COUNT(*) AS COUNTS, SUM(COL3) TOTALVALUEFROM TABLEAGROUP BY COL1, COL2GO - 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:
Post a Comment