1 / 24

SQL server 2008 TSQL Improvements and Data Types

SQL server 2008 TSQL Improvements and Data Types. Михеев Юрий. 29 мая 2008г. 18:30. Module Overview. New Date and Time Data Types Table-Valued Parameters T-SQL Assign and Increment Operators Row Constructors Grouping Sets MERGE statement Dependency Views Performance Enhancements.

bebe
Download Presentation

SQL server 2008 TSQL Improvements and Data Types

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. SQL server 2008TSQL Improvements and Data Types Михеев Юрий 29 мая 2008г. 18:30

  2. Module Overview • New Date and Time Data Types • Table-Valued Parameters • T-SQL Assign and Increment Operators • Row Constructors • Grouping Sets • MERGE statement • Dependency Views • Performance Enhancements

  3. New Date and Time Data Types • SQL Server 2008 extends date/time support • Larger Value Space • Current DATETIME - 1753-9999 Years • Current DATETIME - 0.00333 Second Accuracy • New Date Types - 0001-9999 Years • New Date/Time Types - Precisions to 100 nanoseconds • Variable Precision Saves Space • Separate Date and Time Saves Space • ANSI Compatible

  4. DATE and TIME • DATE Data Type • Date Only • 01-01-0001 to 31-12-9999 Gregorian Calendar • TIME Data Type • Time Only • Variable Precision - 0 to 7 decimal places for seconds • To 100 nanoseconds

  5. DATETIME2 and DATETIMEOFFSET • DATETIME2 Data Type • 01-01-0001 to 31-12-9999 Gregorian Calendar • Variable Precision - to 100 nanoseconds • DATETIMEOFFSET • 01-01-0001 to 31-12-9999 Gregorian Calendar • Variable Precision - to 100 nanoseconds • Time Zone Offset (From UTCTime) Preserved • Not Time Zone Aware - No Daylight Saving Time Support

  6. Date/Time Types Compatibility • New Data Types Use Same T-SQL Functions • DATENAME (datepart, date) • DATEPART (datepart,date) • DATEDIFF (datepart, startdate, enddate) • DATEADD (datepart, number, date) • Datepart can also be microsecond, nanosecond, TZoffset • MONTH • DAY • YEAR • CONVERT extensions

  7. Date Time Library Extensions • Higher precision date/time uses • SYSDATETIME • SYSUTCDATETIME • SYSDATETIMEOFFSET • Original date/time uses • GETDATE, GETUTCDATE, CURRENT_TIMESTAMP • ISDATE(datetime/smalldatetime) • Special functions for DATETIMEOFFSET • SWITCHOFFSET(datetimeoffset, timezone) • TODATETIMEOFFSET(datetime, timezone)

  8. Localization Functions • All Date/Time Types Support Localization • SET DATEFIRST • SET DATEFORMAT • SET LANGUAGE - affects some date/time functions • @@DATEFIRST • @@LANGUAGE

  9. Date/Time and Strings • Dates are input and output in string format • String (varchar) can be output through CAST/CONVERT • CONVERT has extensions for date/time types • Dates are input in a variety of formats • Some of language-sensitive, some not

  10. Table-Valued Parameters • Inserts into structures with 1-n cardinality problematic • One order -> N order line items • "N" is variable and can be large • Don't want to force a new order for every 20 line items • One database round-trip / line item slows things down • No ARRAY data type in SQL Server • XML composition/decomposition used as an alternative • Table-valued parameters solve this problem

  11. Table Types • SQL Server has table variables • DECLARE @t TABLE (id int); • SQL Server 2008 adds strongly typed table variables • CREATE TYPE mytab AS TABLE (id int); • DECLARE @t mytab; • Parameters must use strongly typed table variables

  12. Table Variables are Input Only • Declare and initialize TABLE variable • DECLARE @t mytab; • INSERT @t VALUES (1), (2), (3); • EXEC myproc @t; • Procedure must declare variable READONLY • CREATE PROCEDURE usetable ( • @t mytab READONLY ...) • AS • INSERT INTO lineitems SELECT * FROM @t; • UPDATE @t SET... -- no!

  13. TVP Implementation and Performance • Table Variables materialized in TEMPDB • Faster than parameter arrays, BCP APIs still fastest

  14. T-SQL Syntax Enhancements • Single statement declare and initialize • DECLARE @iint = 4; • Increment Operators • SET @i += 1; • Row constructors • DECLARE @t TABLE (id int, name varchar(20)); • INSERT INTO @t VALUES (1, 'Fred'), (2, 'Jim'), (3, 'Sue');

  15. Grouping Sets • Grouping Sets allow multiple GROUP BY clauses in a single SQL statement • Multiple, arbitrary, sets of subtotals • Single read pass for performance • Nested subtotals provide ever better performance • Grouping Sets are an ANSI-standard • COMPUTE BY is deprecated

  16. MERGE Statement • Multiple set operations in a single SQL statement • Uses multiple sets as input • MERGE target USING source ON ... • Operations can be INSERT, UPDATE, DELETE • Operations based on • WHEN MATCHED • WHEN [TARGET] NOT MATCHED • WHEN SOURCE NOT MATCHED • ANSI SQL 2006 compliant - with extensions

  17. More on MERGE • MERGE statement can reference a $action column • Used when MERGE used with OUTPUT clause • Multiple WHEN clauses possible • For MATCHED and SOURCE NOT MATCHED • Only one WHEN clause for TARGET NOT MATCHED • MERGE can be used with any table source • A MERGE statement causes triggers to be fired once

  18. MERGE Performance • MERGE statement is transactional • No explicit transaction required • One Pass Through Tables • At most a full outer join • Matching rows = when matched • Left-outer join rows = when target not matched • Right-outer join rows = when source not matched

  19. MERGE and Determinism • UPDATE using a JOIN is non-deterministic • If more than one row in source matches ON clause, either/any row can be used for the UPDATE • MERGE is deterministic • If more than one row in source matches ON clause, its an error

  20. Keeping Track of Dependencies • New dependency views replace sp_depends • Dependency views are kept in sync as changes occur • sys.dm_sql_referenced_entities • Lists all named entities that an object references • Example: which objects does this stored procedure use? • sys.dm_sql_referencing_entities • Lists all named entities that use an object • Example: which objects use this table? • Can see references at OBJECT, DATABASE DDL TRIGGER, SERVER DDL TRIGGER level

  21. Performance Enhancements • MERGE and GROUPING SETS offer improvements • Less scans through table • Table-valued parameters offer improvements • Less round trips to database • Improvements for data warehouse queries • Earlier predicate filtering • Multiple bitmap iterators per query • Plan Guide Improvements • Easier to create plan guides • Plan guides on DML statements

  22. Review • New SQL types increase the utility, improve storage requirements of date time type series • Strongly typed table-valued parameters help the database round trip problem • Grouping Sets allow arbitrary group by clauses for subtotals and totals • MERGE statement allows set-to-set comparison and multiple deterministic operations • ANSI standard compliance with extensions

  23. Ссылки • MSDN Webcast: New T-SQL Programmability Features in SQL Server 2008 (Event ID: 1032357753) • BOL: http://msdn.microsoft.com/ru-ru/library/bb543165(sql.100).aspx • http://www.microsoft.com/Rus/sql/2008/default.mspx • WebCasts: http://www.microsoft.com/sqlserver/2008/en/us/events-webcasts.aspx • JumpStart: http://sqlserver2008jumpstart.microsofttraining.com/content/info.asp?CcpSubsiteID=69&infoid=27

  24. Спасибо за внимание yuriymikheev@gmail.com

More Related