what’s new in sql server 2008: t-sql
DESCRIPTION
What’s New in SQL Server 2008: T-SQL. Martin Bell SQL Server MVP. Features we will look at:. Date and Time Functionality Dependency Reporting Filestream Storage Merge Statement Row Constructors Sparse Columns and Column Sets Hierarchy ID Data Type User Defined Table Type - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/1.jpg)
WHAT’S NEW IN SQL SERVER 2008: T-SQL
Martin BellSQL Server MVP
![Page 2: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/2.jpg)
Features we will look at:• Date and Time Functionality• Dependency Reporting• Filestream Storage• Merge Statement• Row Constructors• Sparse Columns and Column Sets• Hierarchy ID Data Type• User Defined Table Type• Table Values Parameters
![Page 3: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/3.jpg)
Date and Time Functionality
• Date• Supports Years 0 – 9999 i.e. 01-01-0001
to 31-12-9999 (Gregorian Calendar)• Native Datatype (not CLR)• Size - 3 bytes
![Page 4: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/4.jpg)
Date and Time Functionality
• Time• Variable Precision - 0 to 7 decimal places
for seconds• Precise to 100 nanoseconds • Separate Date and Time saves space• Ansi compatible• Size - 3 bytes for precisions <= 2;
4 bytes for precisions of 3 and 4 5 bytes for precisions > 4
![Page 5: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/5.jpg)
Date and Time Functionality
• DateTimeOffset• Supports Years 0 – 9999 i.e. 01-01-0001 to 31-12-
9999 (Gregorian Calendar)• Precise to 100 nanoseconds• Time Zone Offset (From UTCTime) Preserved• Not Time Zone Aware - No Daylight Saving Time
Support• Size - 8 bytes for precisions <= 2;
9 bytes for precisions of 3 and 4 10 bytes for precisions > 4
![Page 6: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/6.jpg)
Date and Time Functionality
• DateTime2• Supports Years 0 – 9999 i.e. 01-01-0001 to 31-12-
9999 (Gregorian Calendar)• Precise to 100 nanoseconds• Size - 6 bytes for precisions <= 3;
7 bytes for precisions of 4 and 5 7 bytes for precisions > 5
![Page 7: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/7.jpg)
Date and Time Functionality
• Can still use existing date/time functions• DATENAME (datepart, date)• DATEPART (datepart,date)• DATEDIFF (datepart, startdate, enddate)• DATEADD (datepart, number, date)• Datepart can also be microsecond, nanosecond,
TZoffset or ISO_WEEK• MONTH• DAY• YEAR• CONVERT extensions
![Page 8: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/8.jpg)
Dependency Reporting
• New dependency views replace sp_depends
• Kept in sync as changes occur• sys.dm_sql_referenced_entities • Lists all named entities that an object references
• sys.dm_sql_referencing_entities• Lists all named entities that use an object
• Can see references at OBJECT, DATABASE DDL TRIGGER, SERVER DDL TRIGGER level
![Page 9: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/9.jpg)
Filestream Storage
• Currently limited to 2GB with varchar(max)
• Poor streaming performance• High cost of dataFilestream:• Size limited by NTFS volume• Kept transactionally consistent• Can be stored on compressed volumes
![Page 10: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/10.jpg)
Filestream Storage
• Check out Paul Randal’s Blog for the performance graphs
Filestream reads better than varbinaryFilestream inserts perform better for files over ≈2MB for Win32Varbinary inserts perform than T-SQL FilestreamFilestream updates perform better for files over ≈1MB for Win32Filestream updates perform better than varbinary for T-SQL
![Page 11: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/11.jpg)
Filestream Storage
• SqlFileStream Managed API in VS 2008 SP1
![Page 12: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/12.jpg)
Merge Statement• Lets you INSERT, UPDATE, AND DELETE in a single operation (i.e. single pass of the data)
• Specify commands for each case (Insert, Update or Delete)
• Cannot be remote table of distributed partitioned view
• NOLOCK, READUNCOMMITED disallowed (to maintain index integrity)
• Special $ACTION column allows you to record whether MERGE did an INSERT, UPDATE, DELETE
![Page 13: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/13.jpg)
Merge Statement[ WITH <common_table_expression> [,...n] ]MERGE [ TOP ( expression ) [ PERCENT ] ] [ INTO ] target_table [ [ AS ] table_alias ] [ WITH ( <merge_hint> ) ]USING <table_source>ON <search_condition>[ WHEN MATCHED [ AND <search_condition> ] THEN <merge_matched> ][ WHEN [TARGET] NOT MATCHED [ AND <search_condition> ] THEN <merge_not_matched> ][ WHEN SOURCE NOT MATCHED [ AND <search_condition> ] THEN <merge_ matched> ]<output_clause>[ OPTION ( <query_hint> [ ,...n ] ) ] ;
![Page 14: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/14.jpg)
Row Constructors (Values Clause)
Before SQL 2008:DECLARE @t TABLE (id int, name varchar(20));
INSERT INTO @t VALUESSELECT 1, 'Fred‘UNION ALL SELECT 2, 'Jim‘UNION ALL SELECT 3, 'Sue'
SQL 2008:DECLARE @t TABLE (id int, name varchar(20));
INSERT INTO @t (id, name) VALUES (1, 'Fred'), (2, 'Jim'), (3, 'Sue');
![Page 15: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/15.jpg)
Row Constructors (Values Clause)
SQL 2008:DECLARE @t TABLE (id int, name varchar(20));
INSERT INTO @t (id, name )SELECT num, foreFROM (
VALUES (1, 'Fred'), (2, 'Jim'), (3, 'Sue')) d (num, fore);
![Page 16: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/16.jpg)
Sparse Column and Column Sets
Ways of modeling sparse data:One row per attribute:
256 table limit in SQL Server JOIN
Multiple nullable columns one per attribute:
1024 column limit in SQL Server tables
Modeled as XML:Common properties are elements, sparse
are attributes
![Page 17: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/17.jpg)
Hierarchy Id Data Type
• Compact way to store hierarchies with SELECT query support
• Can allow some hierarchy queries without having to resort to recursive queries
• Slower on re-parenting operations (not just moving pointers!)
• See BOL for discussions of the various trade-offs vs. XML and parent/child tables
![Page 18: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/18.jpg)
Hierarchy Id Data Type
• CLRUDT implementation
• Uses ORDPATH representation (materialized path)
• "Level" property - allows breadth-first indexing
![Page 19: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/19.jpg)
Hierarchy Id Data Type
• Depth First indexing
• Breadth First indexing
![Page 20: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/20.jpg)
Hierarchy Id Data Type
• Methods for common hierarchical operations• GetRoot• GetLevel• IsDescendant• GetDescendant, GetAncestor• Reparent
![Page 21: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/21.jpg)
User Defined Table Type
CREATE TYPE mytab AS TABLE (id int);DECLARE @t mytab;
![Page 22: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/22.jpg)
Table Valued Parameters
How do I pass a table to a T-SQL stored procedure?
How do I efficiently pass multiple values from a database client?
Erland Sommarskog’s Web Sitehttp://www.sommarskog.se/arrays-in-sql.html
![Page 23: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/23.jpg)
Table Valued Parameters
• Parameters must use strongly typed table variables
• Tables are passed by reference
• No Statistics support exposed on these, so be careful in large query plans
![Page 24: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/24.jpg)
References (SQL 2008)
•What’s Newhttp://www.microsoft.com/sqlserver/2008/en/us/overview.aspx• Learning Resourceshttp://www.microsoft.com/sqlserver/2008/en/us/learning.aspx• Technet Insiders Bloghttp://blogs.technet.com/industry_insiders/• Technethttp://technet.microsoft.com/en-us/library/cc721270.aspx
![Page 25: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/25.jpg)
References (Filestream)
• Should I store my images in the database (SQL 2000)
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html• To BLOB or not to BLOBhttp://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45
![Page 26: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/26.jpg)
References (Filestream)
• Paul Randal’s Bloghttp://www.sqlskills.com/blogs/paul/2008/03/09/SQLServer2008FILESTREAMPerformance.aspx• Guy Burstein's Bloghttp://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/12/sqlfilestream-managed-api-for-sql-server-2008-filestream.aspx
![Page 27: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/27.jpg)
References (HierarchyId)
• Simon Sabin’s Bloghttp://sqlblogcasts.com/blogs/simons/• SQL Server Magazinehttp://www.sqlmag.com/articles/index.cfm?articleid=99369
![Page 28: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/28.jpg)
References (Merge)
• Connor Cunningham’s Bloghttp://blogs.msdn.com/conor_cunningham_msft/archive/2009/02/04/conor-vs-merge-and-primary-key-collisions.aspx
![Page 29: What’s New in SQL Server 2008: T-SQL](https://reader036.vdocuments.us/reader036/viewer/2022081422/568161b4550346895dd17efe/html5/thumbnails/29.jpg)
Erland Sommarskog’s Articles http://www.sommarskog.se/arrays-in-sql.html http://www.sommarskog.se/arrays-in-sql-perftest.html
Guy Burstein’s blog http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/12/02/sql-server-2008-
table-valued-parameters.aspx Mike Taulty’s blog
http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/12/03/9991.aspx
References (TVPs)