1 / 29

What’s New in SQL Server 2008: T-SQL

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

masao
Download Presentation

What’s New in SQL Server 2008: T-SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. What’s New in SQL Server 2008: T-SQL Martin Bell SQL Server MVP

  2. 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

  3. 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

  4. Date and Time Functionality • Time • Variable Precision - 0 to 7 decimal places for seconds • Precise to 100 nanoseconds • Separate Date and Time saves space • Ansicompatible • Size - 3 bytes for precisions <= 2; • 4 bytes for precisions of 3 and 4 • 5 bytes for precisions > 4

  5. 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

  6. 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

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

  8. 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

  9. Filestream Storage • Currently limited to 2GB with varchar(max) • Poor streaming performance • High cost of data • Filestream: • Size limited by NTFS volume • Kept transactionallyconsistent • Can be stored on compressed volumes

  10. Filestream Storage • Check out Paul Randal’s Blog for the performance graphs • Filestream reads better than varbinary • Filestream inserts perform better for files over ≈2MB for Win32 • Varbinary inserts perform than T-SQL Filestream • Filestream updates perform better for files over ≈1MB for Win32 • Filestream updates perform better than varbinary for T-SQL

  11. Filestream Storage • SqlFileStream Managed API in VS 2008 SP1

  12. 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

  13. 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 ] ) ] • ;

  14. Row Constructors (Values Clause) • Before SQL 2008: • DECLARE @t TABLE (id int, name varchar(20)); • INSERT INTO @t VALUES • SELECT 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');

  15. Row Constructors (Values Clause) • SQL 2008: • DECLARE @t TABLE (id int, name varchar(20)); • INSERT INTO @t (id, name ) • SELECT num, fore • FROM ( • VALUES (1, 'Fred'), (2, 'Jim'), (3, 'Sue') • ) d (num, fore);

  16. 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

  17. 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

  18. Hierarchy Id Data Type • CLRUDT implementation • Uses ORDPATH representation (materialized path) • "Level" property - allows breadth-first indexing

  19. Hierarchy Id Data Type • Depth First indexing • Breadth First indexing

  20. Hierarchy Id Data Type • Methods for common hierarchical operations • GetRoot • GetLevel • IsDescendant • GetDescendant, GetAncestor • Reparent

  21. User Defined Table Type • CREATE TYPE mytab AS TABLE (id int); • DECLARE @t mytab;

  22. 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? • ErlandSommarskog’s Web Site • http://www.sommarskog.se/arrays-in-sql.html

  23. 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

  24. References (SQL 2008) • What’s New • http://www.microsoft.com/sqlserver/2008/en/us/overview.aspx • Learning Resources • http://www.microsoft.com/sqlserver/2008/en/us/learning.aspx • Technet Insiders Blog • http://blogs.technet.com/industry_insiders/ • Technet • http://technet.microsoft.com/en-us/library/cc721270.aspx

  25. 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 BLOB • http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

  26. References (Filestream) • Paul Randal’s Blog • http://www.sqlskills.com/blogs/paul/2008/03/09/SQLServer2008FILESTREAMPerformance.aspx • Guy Burstein's Blog • http://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/12/sqlfilestream-managed-api-for-sql-server-2008-filestream.aspx

  27. References (HierarchyId) • Simon Sabin’s Blog • http://sqlblogcasts.com/blogs/simons/ • SQL Server Magazine • http://www.sqlmag.com/articles/index.cfm?articleid=99369

  28. References (Merge) • Connor Cunningham’s Blog • http://blogs.msdn.com/conor_cunningham_msft/archive/2009/02/04/conor-vs-merge-and-primary-key-collisions.aspx

  29. References (TVPs) • ErlandSommarskog’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

More Related