240 likes | 571 Views
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.
E N D
SQL server 2008TSQL 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
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
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
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
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
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)
Localization Functions • All Date/Time Types Support Localization • SET DATEFIRST • SET DATEFORMAT • SET LANGUAGE - affects some date/time functions • @@DATEFIRST • @@LANGUAGE
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
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
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
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!
TVP Implementation and Performance • Table Variables materialized in TEMPDB • Faster than parameter arrays, BCP APIs still fastest
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');
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
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
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
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
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
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
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
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
Ссылки • 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
Спасибо за внимание yuriymikheev@gmail.com