SQL Server: March 2012

SQL Server 2008 New TSQL Programmability Features at a glance

1 New Data Types

A Date and Time Data types

DATE

Defines the date.

DECLARE @MyDate DATE

CREATE TABLE Table1 (Column1 DATE)

*** YYYY-MM-DD

*** Follows Gregorian calendar

*** Default value is 1900-01-01

*** Date range 0001-01-01 to 9999-12-31

*** Accuracy 100 nano seconds

*** Storage size 3 bytes fixed

DECLARE @d DATE

SET @d = GETDATE()

SELECT @d

-- output '2012-03-12'

SET @d = '1234-03-12 16:30:00'

SELECT @d

-- output '1234-03-22'

TIME (Fractional Second Precision)

Defines the time of a day.

We can specify the optional precision (default seven digits) during declaration.

DECLARE @MyTime TIME(7)

CREATE TABLE Table1 (Column1 TIME(7) )

*** hh:mm:ss [.nnnnnnn]

*** Default 00:00:00

*** Accuracy 100 nano seconds

*** Storage size 5 bytes fixed

CREATE TABLE #times(

Time_DefaultPrecision TIME,

Time_Precision1 TIME(1),

Time_Precision5 TIME(5),

Time_Precision7 TIME(7)

)

INSERT INTO #times VALUES (

'16:30:01.7654321',

'16:30:01.7654321')

SELECT * FROM #times

DROP TABLE #times

--output

Time_DefaultPrecision Time_Precision1 Time_Precision5 Time_Precision7

--------------------- ---------------- ---------------- ----------------

16:30:01.7654321 16:30:01.8 16:30:01.76543 16:30:01.7654321

DATETIME2 (Fractional Second Precision)

It is an extension to the DATETIME data type.

It has extended precision up to seven digits.

We can specify the optional precision (default seven digits) during declaration.

DECLARE @MyDatetime2 DATETIME2(7)

CREATE TABLE Table1 (Column1 DATETIME2(7) )

*** YYYY-MM-DD hh:mm:ss [.nnnnnnn]

*** Follows Gregorian calendar

*** Default value for DATETIME2 is 1900-01-01

*** Date range 0001-01-01 to 9999-12-31

*** Accuracy 100 nano seconds

*** Storage size

Precision < 3 - 6 bytes

Precision 3, 4 - 7 bytes

Precision > 4 - 8 bytes

DECLARE @dateA DATETIME2 = '2012-03-12 16:30:01.7654321'

PRINT @dateA -- output 2012-03-12 16:30:01.7654321

DECLARE @dateB DATETIME2(4) = '2012-03-12 16:30:01.7654321'

PRINT @dateB -- output 2012-03-12 16:30:01.7654

DATETIMEOFFSET (Fractional Second Precision)

It is an extension to the DATETIME data type.

It has extended precision up to seven digits.

We can specify the optional precision (default seven digits) during declaration.

DECLARE @MyDatetime2 DATETIME2(7)

CREATE TABLE Table1 (Column1 DATETIME2(7) )

*** YYYY-MM-DD hh:mm:ss [.nnnnnnn]

*** Follows Gregorian calendar

*** Default value for DATETIME2 is 1900-01-01

*** Date range 0001-01-01 to 9999-12-31

*** Accuracy 100 nano seconds

*** Storage size 10 Bytes fixed

SET NOCOUNT ON

-- Time Stamp on Central Daylight Time

DECLARE @today DATETIMEOFFSET = '2012-03-12 01:02:03.1234567-05:00'

DECLARE @today1 DATETIMEOFFSET = '2012-03-12T01:02:03.1234567-05:00'

DECLARE @today2 DATETIMEOFFSET(2) = '2012-03-12 01:02:03.1234567-05:00'

DECLARE @today3 DATETIMEOFFSET(2) = '2012-03-12T01:02:03.1234567-05:00'

SELECT @today -- output 2012-03-12 01:02:03.1234567 -05:00 ,

, @today1 -- output 2012-03-12 01:02:03.1234567 -05:00

, @today2 -- output 2012-03-12 01:02:03.12 -05:00

, @today3 -- output 2012-03-12 01:02:03.12 -05:00

SELECT

CAST('2012-03-12 16:30:00. 1234567 +10:11' AS TIME(7)) AS [Time]

,CAST('2012-03-12 16:30:00. 1234567 +10:11' AS DATE) AS [Date]

,CAST('2012-03-12 16:30:00.123' AS SMALLDATETIME) AS [SmallDateTime]

,CAST('2012-03-12 16:30:00.123' AS DATETIME) AS [DateTime]

,CAST('2012-03-12 16:30:00.1234567+10:11' AS DATETIME2(7)) AS [DateTime2]

,CAST('2012-03-12 16:30:00.1234567 +10:11' AS DATETIMEOFFSET(7)) AS [DateTimeOffset]

B Spatial Data types – Geometry and Geography:

SQL Server 2008 introduces the spatial data types to simplify the development process (like combining the spatial data with other data).

Geometry data type stores planar data like the (X, Y) co-ordinates on the surface of the earth to represent a point, line or a polygon in a two-dimensional space.

CREATE TABLE SpatialDemo

(

DataID INT IDENTITY(1,1)NOT NULL,

--Using GEOMETRY data type similar to any other system defined data type

SpatialData GEOMETRY

)

INSERT INTO SpatialDemo (SpatialData)

VALUES

--insert a point where X = 5 and Y = 10

('POINT(5 10)'),

--insert a line start from X = 0 and Y = 0

--to X = 5 and Y = 5

('LINESTRING(0 0, 5 5)'),

--insert a line start from X = 0 and Y = 0 to X = 5 and Y = 5 and then to X = 7 and Y = 3

('LINESTRING(0 0, 5 5, 7 3)'),

--insert a polygon with triangular shape

('POLYGON((0 0, 8 5, 8 5, 10 0, 0 0))')

--Note

SELECT * FROM SpatialDemo

--A space is used to separate the coordinates of a point. A comma is used to separate points within a more complex type.

--Parentheses are used to group points into a single shape.

Geography data type stores ellipsoidal data like the latitudes and longitudes that represents a point, line or a polygon on earth’s surface.

*** Support special spatial indexes

C HierarchyId

It is used to create tables with a hierarchical structure or to reference hierarchical structure of data in another location.

Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships. Hierarchical relationships are where one item of data is the parent of another item. Hierarchical data is common in databases.

Examples include the following:

*** An organizational structure

*** A file system

*** A set of tasks in a project

*** Taxonomy of language terms

*** A graph of links between Web pages

The methods used in the HierarchyId are

GetRoot - It returns root of the hierarchy tree. HIERARCHYID::GetRoot()

GetAncestor - It is used to find nth ancestos (parents and grantparents) of the given node.

child.GetAncestor () (where represents the number of levels to go up in the hierarchy)

GetDescendant - It returns a child node of the given parent. It is mainly used to determine new descendant's position before adding it to tree.

Parent.GetDescendant (, )

GetLevel - It returns the depth of the node under the provided node/tree.

node.GetLevel ( )

IsDescendantOf - It returns true if child is a descendant of provided node. In other words, It is used to determine if a record or value is a child of a second record or value.

Parent.IsDescendantOf()

Parse It is again a static method so it will be called by HIERARCHYID type itself. It is used to convert the canonical version of the data type to a HIERARCHYID value. Parse is called implicitly when a conversion from a string type to HIERARCHYID occurs. It acts as the opposite of ToString method discussed below.

HIERARCHYID::Parse ( input )

Read This method is only available within .NET CLR code. It is used to read the binary representation of the HIERARCHYID value and cannot be called by using Transact-SQL.

void Read( BinaryReader r )

GetReparentedValue - This method helps to re-parent a node i.e. suppose a manager left a job and all his directs will now come under another manager, it will be much helpful in this kind of scenario. In other words, it can be used to modify the tree by moving nodes from oldRoot to newRoot.

node. GetReparentedValue ( , )

ToString - It gives string representation (logical representation) of the HIERARCHYID. The data type for returned value is nvarchar(4000).

node.ToString ( )

Write Similar to the Read method as discussed above, this method is also available only within .NET CLR code and cannot be called by using Transact-SQL. It writes out a binary representation of SqlHierarchyId to the passed-in BinaryWriter.

void Write( BinaryWriter w )

-- our Categories table

CREATE TABLE #Categories (

CategoryID INT IDENTITY(1,1),

CategoryNode HIERARCHYID NOT NULL,

CategName NVARCHAR(40) NOT NULL

)

-- the root category

DECLARE @root HIERARCHYID = hierarchyid::GetRoot()

INSERT INTO #Categories (CategoryNode, CategName)

VALUES (@root, 'All #Categories')

-- insert the 'Electronics' category

DECLARE @electronics HIERARCHYID

SELECT @electronics = @root.GetDescendant(NULL, NULL)

INSERT INTO #Categories (CategoryNode, CategName)

VALUES (@electronics, 'Electronics')

-- insert the 'Music' category after 'Electronics'

DECLARE @music HIERARCHYID

SELECT @music = @root.GetDescendant(NULL, @electronics)

INSERT INTO #Categories (CategoryNode, CategName)

VALUES (@music, 'Music')

-- insert the 'Apparel' category between 'Electronics' and 'Music'

SELECT @music = @root.GetDescendant(@music, @electronics)

INSERT INTO #Categories (CategoryNode, CategName)

VALUES (@music, 'Apparel')

-- insert some children under 'Electronics'

DECLARE @video HIERARCHYID

-- We could do a simple @category.GetDescendant() but, let's

-- show something that is more likely to happen

SELECT @video = CategoryNode.GetDescendant(NULL, NULL)

FROM #Categories WHERE CategName ='Electronics'

INSERT INTO #Categories (CategoryNode, CategName)

VALUES (@video, 'Video Equipment')

-- insert some children under 'Video Equipment'

DECLARE @tvs HIERARCHYID

SELECT @tvs = @video.GetDescendant(NULL, NULL)

INSERT INTO #Categories (CategoryNode, CategName)

VALUES (@tvs, 'Televisions')

DECLARE @players HIERARCHYID

SELECT @players = @video.GetDescendant(NULL, @tvs)

INSERT INTO #Categories (CategoryNode, CategName)

VALUES (@players, 'DVD - BluRay')

--When we query the table, the output from the CategoryNode column reflects the position of the record in the hierarchy

--, similar to directory paths.

SELECT

CategoryID, CategName,

CategoryNode,

CategoryNode.ToString() AS Path

FROM #Categories

DROP TABLE #Categories

2 Table Valued Parameter

A tabular data can be passed as a parameter to the routines. This mechanism minimizes a lot of effort where previously one by one parameter set is used to be passed.

*** Efficient transport of data from client to Server

*** Need to create a User-defined table type to use the TVP

*** Limitations of User-defined table types

Once created, their definition cannot be modified

We cannot perform DML operations on them once they are passed to the routine (READONLY)

Cannot be used as OUTPUT variables

SQL Server does not store statistics for them

CREATE TABLE TVPTest(Id INT, Name VARCHAR(50))

INSERT INTO TVPTest

SELECT 1, 'abc' UNION ALL

SELECT 2, 'bcd' UNION ALL

SELECT 3, 'cde' UNION ALL

SELECT 4, 'def' UNION ALL

SELECT 5, 'efg'

CREATE TYPE TVPtbl AS TABLE (Id INT, Name VARCHAR(50))

CREATE PROCEDURE dbo.TVPTestProc(@tvp_variable TVPtbl READONLY)

BEGIN

UPDATE T SET T.Name = V.Name

FROM TVPTest AS T

INNER JOIN @tvp_variable AS V ON T.Id = V.Id

END

SELECT * FROM TVPTest

DECLARE @tbl AS TVPtbl

INSERT INTO @tbl

SELECT 1, 'aaa' UNION ALL

SELECT 2, 'bbb' UNION ALL

SELECT 3, 'ccc' UNION ALL

SELECT 4, 'ddd' UNION ALL

SELECT 5, 'eee'

EXEC dbo.TVPTestProc @tvp_variable = @tbl

SELECT * FROM TVPTest

DROP PROCEDURE TVPTestProc

DROP TYPE TVPtbl

DROP TABLE TVPTest

3 Merge Statement

Merge Statement has the ability to perform multiple DML (INS/ UPD/ DEL) operations in a single statement.

*** Useful to synchronize target with the sources

*** Can perform UPSERTS (INSERT + UPDATE)

*** Since all the DML can be processed in a single statement, it increases the performance of the query

MERGE INTO Taget AS T USING Source AS S ON Condition

WHEN MATCHED AND (T.Col2 != S.Col2 OR T.Col3 != S.Col3) THEN -- Row Exists, data is different

UPDATE SET T.Col2 = S.Col2, T.Col3 = S.Col3

WHEN NOT MATCHED THEN -- Row exists in source but not in target

INSERT VALUES(S.Col1, S.Col2....,S.Coln)

WHEN SOURCE NOT MATCHED THEN -- Row not in Source but found in target

DELETE;

CREATE TABLE MergeTarget (ProductId INT, TotalSale INT)

CREATE TABLE MergeSource (ProductId INT, QtySoldToday INT)

INSERT MergeSource

SELECT 1, 12 UNION ALL

SELECT 2, 20 UNION ALL

SELECT 3, 15 UNION ALL

SELECT 4, 27 UNION ALL

SELECT 5, 15 UNION ALL

SELECT 6, 25 UNION ALL

SELECT 7, 12

INSERT MergeTarget

SELECT 1, 8 UNION ALL

SELECT 2, 20 UNION ALL

SELECT 3, 15 UNION ALL

SELECT 4, 27 UNION ALL

SELECT 5, 15 UNION ALL

SELECT 6, 12 UNION ALL

SELECT 10, 99

SELECT * FROM MergeSource

SELECT * FROM MergeTarget

SELECT * INTO #T FROM MergeTarget WHERE 1 = 0 -- Temp table to track the changes

CREATE TABLE #T (Action VARCHAR(50), ProductId INT, NewTotalQty INT, PreviousTotalQty INT)

MERGE MergeTarget AS T USING MergeSource AS S ON T.ProductId = S.ProductId

WHEN MATCHED THEN

UPDATE SET T.TotalSale += S.QtySoldToday

WHEN NOT MATCHED THEN

INSERT VALUES (S.ProductId, QtySoldToday)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT $action , Inserted.ProductID, Inserted.TotalSale AS NewQty, Deleted.TotalSale AS PreviousQty INTO #T;

SELECT * FROM #T

SELECT * FROM MergeSource

SELECT * FROM MergeTarget

DROP TABLE MergeSource

DROP TABLE MergeTarget

DROP TABLE #T

4 Large UDTs

Large user-defined types allow users to expand the size of defined data types by eliminating the 8-KB limit. SQL Server 2005 allowed for user defined types (UDTs) and user defined aggregates in the CLR up to 8000 bytes only, but SQL Server 2008 increases this all the way to 2GB.

If the UDT value does not exceed 8,000 bytes, the database system treats it as an inline value as in SQL Server 2005. If it exceeds 8,000 bytes, the system treats it as a large object and reports its size as “unlimited.”

5 SQL CLR Enhancements

*** Large Aggregates - SQL Server 2008 allows for user defined aggregates in the CLR all the way to 2GB erasing the 8K limit.

*** Easy access to .Net Framework functionality

*** Ability to register static methods as UDFs

*** Multi input aggregates

*** Specifying sort order and uniqueness to TVFs

6 Grouping Sets

It is an extension to the GROUP BY clause. It gives the ability to perform multiple groupings in the same query that gives the result set UNION ALL of the groupings made individually on the query.

*** Makes aggregation querying and reporting much faster

CREATE TABLE #Cars (Id INT, MakeYear INT, Colour VARCHAR(30))

INSERT INTO #Cars

SELECT 1, 2005, 'Blue' UNION ALL

SELECT 2, 2005, 'Red' UNION ALL

SELECT 3, 2006, 'Red' UNION ALL

SELECT 4, 2006, 'Blue' UNION ALL

SELECT 5, 2007, 'Red' UNION ALL

SELECT 6, 2007, 'Blue' UNION ALL

SELECT 7, 2008, 'Red' UNION ALL

SELECT 8, 2008, 'Red'

SELECT COUNT(*) AS Knt, MakeYear FROM #Cars GROUP BY MakeYear

SELECT COUNT(*) AS Knt, Colour FROM #Cars GROUP BY Colour

SELECT COUNT(*) AS Knt, Colour, MakeYear

FROM #Cars

GROUP BY GROUPING SETS ((Colour)

, (MakeYear)

)

DROP TABLE #Cars

7 Object Dependencies Enhancements

*** Reliable discovery of dependencies between the objects

*** Applies to both schema-bound (where an object cannot be deleted as another object depends on it) and non-schema-bound objects (where an object can be deleted and the other object still depends on it).

*** Discover all objects that

* Depends on a given object

* A given object depends

* Depends on another database

* Perform distributed queries using four-part names

Sys.sql_expression_dependencies

* New ctatalog view that replaces sys.sql_dependencies

* Traces schema-bound and non-schema-bound dependencies

* Traces cross-database and cross-server references

SELECT * FROM Sys.sql_expression_dependencies

WHERE referenced_id = OBJECT_ID(N'Production.Product')

Sys.dm_sql_referenced_entities

* New Dynamic management view

* Replaces sp_depends

* Returns a row for each entity referenced by a given object

SELECT referenced_schema_name, referenced_entity_name, referenced_id, referenced_class_desc, is_caller_dependent

FROM sys.dm_sql_referenced_entities ('MyDBObject', 'OBJECT');

Sys.dm_sql_referencing_entities

* New Dynamic management view

* Replaces sp_depends

* Returns a row for each entity referenced by a given object

SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent

FROM sys.dm_sql_referencing_entities ('MyDBObject', 'OBJECT');

8 T-SQL Delighters

Variable Initialization during Declaration

DECLARE @int INT = 6, @char VARCHAR(20) = 'String'

SELECT @int AS Innteger, @char AS String

Insert multiple rows through VALUE clause of a single insert statement

CREATE TABLE #MusicInstruments(Id INT, Instrument VARCHAR(50))

INSERT INTO #MusicInstruments

VALUES(1, 'Guitar')

,(2, 'Flute')

,(3, 'Piano')

,(4, 'Table')

,(5, 'Veena')

SELECT * FROM #MusicInstruments

DROP TABLE #MusicInstruments

Extending the CONVERT functionality – to convert between the binary hexadecimal data and character data.

SELECT CONVERT(VARCHAR(MAX), SpatialData) AS ConvertedHex FROM SpatialDemo

New collations (for say to add culture – correct weight etc) are introduced.

Tuesday, March 13, 2012

SQL Server 2008 New TSQL Programmability Features at a glance