SQL Server 2008 New Features for Developers

1. SQL Server 2008 New Features for Developers Peter DeBetta

2. Introduction • Peter DeBetta • Trainer, Programmer, Architect, Consultant, Author • MSDN Magazine • Microsoft MVP – SQL Server • Introduction to SQL Server 2005 for Developers • Introduction to SQL Server 2008 • SQLblog (http://www.sqlblog.com)

3. Overview • Transact-SQL Enhancements • Assignment & Row Constructors • Table-valued Parameters • Merge • Grouping Sets • CLR Improvements • New and Enhanced Data Types • Data and Time • HierarchyID • Spatial data • Filestream • Other Features

4. Assignment Operators • New Incremental Assignment Operators • Addition: += • Subtraction: -= • Multiplication: *= • Division: /= • Modulo: %= • Declare and assign in a single statement • Can use previously defined variables or static values • Cannot use incremental assignment operators

5. Assignment Operators USE AdventureWorks; GO UPDATE Production.Product SET ListPrice *= 1.05 OUTPUT inserted.ProductID, deleted.ListPrice AS OldPrice, inserted.ListPrice AS NewPrice WHERE ProductID = 680;

6. Assignment Operators DECLARE @i INT = 10 DECLARE @xml XML = '<root><item id="1">A</item><item id="2">B</item></root>' DECLARE @i INT = 10 DECLARE @n INT += @i –Incremental Assignment not valid in DECLARE SELECT @i, @n DECLARE @i INT = 10 DECLARE @n INT = @i + 8 SET @n -= @i SET @i *= 2 SELECT @i, @n

7. Row Constructors • VALUES clause can now return multiple rows as a single [atomic] table • When used with a CTE • Replaces the need to define and insert into a • Temporary table • Table variable • When used with INSERT • Replaces the SELECT … UNION ALL SELECT … methodology for loading multiple rows into a table

8. Row Constructors CREATE TABLE AutomatedPolicyExecutionMode ( ModeId INT NOT NULL PRIMARY KEY, ModeName VARCHAR(100) ) GO INSERT INTO AutomatedPolicyExecutionMode (ModeId, ModeName) VALUES (0, 'On demand'), (1, 'Enforce Compliance'), (2, 'Check on change and log'), (4, 'Check on schedule and log‘)

9. Row Constructors ;WITH AutomatedPolicyExecutionMode (ModeId, ModeName) AS (SELECT * FROM (VALUES (0, 'On demand'), (1, 'Enforce Compliance'), (2, 'Check on change and log'), (4, 'Check on schedule and log') ) AS EM(ModeId, ModeName))SELECT pmf.[management_facet_id] AS FacetID , pmf.[name] AS FacetName , APEM.[ModeName]FROM syspolicy_management_facets AS pmf INNER JOIN AutomatedPolicyExecutionMode AS APEM ON pmf.[execution_mode] & APEM.[ModeId] = APEM.[ModeId]ORDER BY pmf.[name], APEM.[ModeId]

10. Table-Valued Parameters • New user-defined table type • Reusable table definition for table variables • Can define local variable of table type • Instead of defining table structure, use table type instead • You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement

11. Table-Valued Parameters • Stored Procedures and User-Defined Functions can use table type as input parameter • Must be read-only (no DML on table-valued parameters) • Cannot be used for output • SQL Server does not maintain statistics on columns of table-valued parameters. • Locks are not acquired when initially populating table-valued parameter data from a client • Do not cause a statement to recompile

12. Table-Valued Parameters CREATE TYPE dbo.NewPerson AS TABLE ( PersonID INT NOT NULL PRIMARY KEY, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(30) NOT NULL ); GO DECLARE @T AS dbo.NewPerson; INSERT INTO @T(PersonID, FirstName, LastName) VALUES(1, 'Peter', 'DeBetta'),(2, 'Adam', 'Machanic'),(3, 'Jane', 'Doe') SELECT * FROM @T;

13. Table-Valued Parameters CREATE PROC dbo.prPersonDetail (@T AS dbo.NewPerson READONLY) AS SELECT C.* FROM Person.Contact AS C INNER JOIN @T AS T ON C.ContactID = T.PersonID; GO DECLARE @MyPeople AS dbo.NewPerson; INSERT INTO @MyPeople(PersonID, FirstName, LastName) VALUES(1, 'Peter', 'DeBetta'),(2, 'Adam', 'Machanic'),(3, 'Jane', 'Doe') EXEC dbo.prPersonDetail @T = @MyPeople;

14. MERGE • The mythical UPSERT • An single, atomic operation that combines the functionality of INSERT, UPDATE, and DELETE • Semicolon terminator is required! • $action returns action type as string • INSERT, UPDATE, DELETE • If you define an INSTEAD OF trigger for any action on the target table, there must be an INSTEAD OF trigger for all actions on the target table.

15. MERGE Syntax MERGE <target_table> [ AS table_alias ] USING <table_source> ON <search_condition> [WHEN MATCHED [ AND <search_condition>] THEN {UPDATE… | DELETE} ] [WHEN NOT MATCHED BY TARGET [ AND <search_condition>] THEN INSERT…   ] [WHEN NOT MATCHED BY SOURCE [ AND <search_condition>] THEN {UPDATE… | DELETE} ] ;

16. MERGE • MERGE does outer joins between the target and source data as needed. • WHEN NOT MATCHED BY TARGET performs an outer join from the source table to the target table • WHEN NOT MATCHED BY SOURCE performs an outer join from the target table to the source table. • When using both the WHEN NOT MATCHED BY TARGET and WHEN NOT MATCHED BY SOURCE clauses in a single MERGE statement • A full outer join is used!!!

17. MERGE • WHEN MATCHED • Find matches between the target and source tables • Can either perform an UPDATE or DELETE against the target table • Can be used at most two times in the MERGE statement • WHEN NOT MATCHED BY TARGET • The keyword TARGET is optional, although I suggest being explicit • Determine if there are rows in the source table that don’t exist in the target. • This is the only WHEN clause that can INSERT data into the source table • Can be used at most one time in the MERGE statement

18. MERGE • WHEN NOT MATCHED BY SOURCE • Find rows in the target table that do not exist in the source table • Can be used at most two times in the MERGE statement. • If used twice the following also applies • The two clauses are processed in order • One clause must UPDATE and the other one must DELETE (order is not important) • The second clause is checked only if the first is not satisfied • The first clause must specify additional criteria:If you attempt to execute without specifying additional search criteria for the first clause, you will receive an error.

19. MERGE Example WITH CustSales (CustomerID, MaxOrderDate , TotalDueTotal) AS (SELECT CustomerID, MAX(OrderDate), SUM(TotalDue) FROM SalesLT.SalesOrderHeader GROUP BY CustomerID) MERGE SalesLT.CustomerTotals AS ct USING CustSales ON CustSales.CustomerID = ct.CustomerID WHEN MATCHED THEN UPDATE SET LastOrderDate = cs.MaxOrderDate, SalesTotal = cs.TotalDueTotal WHEN NOT MATCHED BY SOURCE THEN DELETE WHEN NOT MATCHED BY TARGET THEN INSERT (CustomerID, LastOrderDate, SalesTotal) VALUES (CustSales.CustomerID, CustSales.MaxOrderDate, CustSales.TotalDueTotal); OUTPUT $action, deleted.*, inserted.*;

20. Grouping Sets • Grouping sets are literally a set of grouping columns • Enhancements related to grouping sets • GROUP BY sub-clauses • GROUPING SETS: Manual list of sets • CUBE: 2n grouping sets constructed from n elements, creates aggregate values for all elements • ROLLUP: n+1 grouping sets constructed from n elements, creates aggregate values for elements from right to left in sub-clause • New GROUPING_ID function identifies grouping set association

21. Grouping Sets Equivalents SELECT C1, C2, C3, SomeAgg(C4) FROM T GROUP BY ROLLUP(C1, C2, C3) -- is equivalent to SELECT C1, C2, C3, SomeAgg(C4) FROM T GROUP BY GROUPING SETS ( (C1, C2, C3) ,(C1, C2) ,(C1) ,() ) -- is equivalent to SELECT C1, C2, C3, SomeAgg(C4) FROM T GROUP BY (C1, C2, C3) UNION ALL SELECT C1, C2, NULL, SomeAgg(C4) FROM T GROUP BY (C1, C2) UNION ALL SELECT C1, NULL, NULL, SomeAgg(C4) FROM T GROUP BY(C1) UNION ALL SELECT NULL, NULL, NULL, SomeAgg(C4) FROM T

22. Grouping Sets Equivalents GROUP BY CUBE (C1, C2, C3) -- is equivalent to GROUP BY GROUPING SETS ( (C1, C2, C3) ,(C1, C2) ,(C1, C3) ,(C2, C3) ,(C1) ,(C2) ,(C3) ,() )

23. Grouping Sets Examples SELECT S.TerritoryID, Year(S.OrderDate) AS SaleYear, SUM(S.TotalDue) AS TotalDueTotal FROM Sales.SalesOrderHeader AS S GROUP BY CUBE(TerritoryID, Year(S.OrderDate)) -- is equivalent to SELECT S.TerritoryID, Year(S.OrderDate) AS SaleYear, SUM(S.TotalDue) AS TotalDueTotal FROM Sales.SalesOrderHeader AS S GROUP BY GROUPING SETS ((TerritoryID, Year(S.OrderDate)), (Year(S.OrderDate)), (TerritoryID), ())

24. CLR Enhancements • In SQL Server 2005, UDTs are limited to 8000 bytes • In SQL Server 2008, UDTs can be up to 2GB. • Essentially varbinary(max) • UDT define their size • Large UDTs can have a size of -1 (equivalent to “max”) • This size will be reflected as -1 in type metadata • Large UDTs will be converted to varbinary(max) or image as needed for clients

25. Date and Time Data Type

26. Date and Time Data Type • New functions • SYSDATETIME • SYSUTCDATETIME • SYSDATETIMEOFFSET • TODATETIMEOFFSET • SWITCHOFFSET • DATEPART and DATENAME “part” additions • microsecond • Nanosecond • TZoffset • ISO_WEEK

27. HIERARCHYID Data Type • New system CLR type supporting trees • Uses a materialized path methodology • Underlying binary representation of path • HierarchyID methods • GetRoot ,GetAncestor, GetDescendant • GetLevel, IsDescendant • Reparent (move a node) • Parse, ToString

28. HIERARCHYID Data Type CREATE TABLE HumanResources.EmployeeOrg ( OrgNode hierarchyid PRIMARY KEY CLUSTERED, -- depth OrgLevel AS OrgNode.GetLevel() PERSISTED, EmployeeID int UNIQUE NOT NULL, EmpName varchar(20) NOT NULL, Title varchar(20) NULL ) ; GO CREATE UNIQUE INDEX EmployeeOrgNc1 --breadth ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ;

29. HIERARCHYID – Add Child Node -- From SQL Server Books Online CREATE PROC prAddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20)) AS BEGIN DECLARE @mOrgNode hierarchyid, @lc hierarchyid SELECT @mOrgNode = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = @mgrid SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT @lc = max(OrgNode) FROM HumanResources.EmployeeOrg WHERE OrgNode.GetAncestor(1) = @mOrgNode ; INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title) COMMIT END

30. HIERARCHYID Data Type – Querying --Sariya's subordinates DECLARE @CurrentEmployee hierarchyid SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = 46 ; SELECT * FROM HumanResources.EmployeeOrg WHERE @CurrentEmployee.IsDescendant(OrgNode) = 1 ; -- Path Presentation SELECT REPLICATE(' | ', OrgLevel) + EmpName AS EmpName, OrgNode.ToString() AS OrgPath FROM HumanResources.EmployeeOrg ORDER BY OrgNode;

31. Geodetic Type • New GEOGRAPHY data type • GEOGRAPHY can store instances of various types • Points • Line strings • Polygons • Collections of the above • Methods for computing • Spatial relationships: intersects, disjoint, etc. • Spatial constructions: intersection, union, etc. • Metric functions: distance, area

32. Spatial Example Code CREATE TABLE SpatialTable ( id int IDENTITY (1,1), GeomColumn geometry ) INSERT INTO SpatialTable (GeomCol1) VALUES (geometry::STGeomFromText('LINESTRING (0 2, 2 0, 4 2)', 0)); DECLARE @g geometry; SET @g = geometry::STGeomFromText('POINT(1 1)', 0); SELECT @g.STIntersects(@h) FROM SpatialTable WHERE GeomColumn.STIntersects(@g)

33. What Spatial Types Cannot Do • Raster data • 3 dimensional • Topology • Points make up LineStrings • LineStrings make up Polygons • Network models • Distance between cities along the road network

34. FILESTREAM • BLOBs in the file system • Managed by SQL Server • You can use T-SQL to manage BLOB data or • You can use Win32 APIs to work with the files • PathName returns a virtual path as a token to a BLOB • GET_FILESTREAM_TRANSACTION_CONTEXT() returns a session transaction token • Use Win32 APIs • The OpenSqlFilestream for the file handle • ReadFile, WriteFile, TransmitFile, and so on… • The application should close the handle by using CloseHandle

35. FileStream • Must turn on filestream access • Windows Layer • SQL Server Configuration Manager • SQL Instance Layer • sp_configure • 'filestream access'

36. FileStream • New filegroup for storing BLOB data • Filename is a path (not a file) • Last folder in path must not exist • Path up to last folder must exist • On next slide, 'C:\test' must exist, but 'C:\test\Resumes' must not exist • Table definition must also specify the blob data is using filestream • Varbinary(max) column with FILESTREAM keyword

37. FileStream CREATE DATABASE... ..., FILEGROUP FileGroupName CONTAINS FILESTREAM ( NAME = FileStreamDBResumes, FILENAME = N'C:\test\Resumes' ) CREATE TABLE dbo.student ( student_id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, Name varchar(25), Resume varbinary(max) FILESTREAM );

38. Review • Transact-SQL Enhancements • Assignment & Row Constructors • Table-valued Parameters • Merge • Grouping Sets • CLR Improvements • New and Enhanced Data Types • Data and Time • HierarchyID • Spatial data • Filestream • Other Features

39. Questions