1 / 22

Meeting Date: August 23, 2007 Topic: New Features of Katmai by Aaron Bertrand

Meeting Date: August 23, 2007 Topic: New Features of Katmai by Aaron Bertrand. User Group Update. July Meeting Successful >40 registered attendees today >15 new registered members Upcoming meetings: Sept – Andrew Kelly Performance expert & SQL Server MVP

ianthe
Download Presentation

Meeting Date: August 23, 2007 Topic: New Features of Katmai by Aaron Bertrand

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. Meeting Date: August 23, 2007 Topic: New Features of Katmai by Aaron Bertrand

  2. User Group Update July Meeting Successful >40 registered attendees today >15 new registered members Upcoming meetings: Sept – Andrew Kelly Performance expert & SQL Server MVP Oct – Rick Heiges (physical meeting) Noted Speaker & PASS Board Member Nov – Brian Knight Author and SQL Server MVP

  3. Today’s Speaker Aaron Bertrand DBA and Application Architect Manages large cutting-edge SQL Server Enterprise Supports the SQL Community Major poster to Microsoft's public newsgroups for SQL Server, and connect.microsoft.com Blogs at SQL Blog.com SQL Server MVP

  4. What’s New in Katmai(SQL Server 2008) Aaron Bertrand SQL Server MVP aaron.bertrand@cox.net

  5. Overview • New Date / Time data types : • DATE / TIME • DATETIME2 • DATETIMEOFFSET • New T-SQL statement : MERGE • Change Data Capture (CDC) • Table-Valued Parameters • Declarative Management Framework (DMF) • GROUPING SETS • Performance Warehouse • New data type : HierarchyID • … other changes …

  6. DATE / TIME • DATE • January 1st, 0001 A.D.  December 31st, 9999 • Precision : 1 day • Storage : 3 bytes • Some surprising limitations in current CTPs • TIME • 00:00:00.0000000 (midnight)  23:59:59.9999999 • Precision : default is 7 decimal places (100 ns) • Storage : 3 – 5 bytes, depending on precision • DEMO

  7. DATETIME2 • January 1st, 1 A.D.  December 31st, 9999 23:59:59.9999999 • Precision : 100 ns, up to 7 decimal places (7 is the default) • Storage : 6 – 8 bytes, depending on precision • New system functions that return DATETIME2 values: • SYSDATETIME() • SYSUTCDATETIME() • New arguments for DATEPART, DATEDIFF: • MICROSECOND • NANOSECOND • CTP has some limitations that may not be corrected(and no, they won’t consider changing the name) • DEMO

  8. DATETIMEOFFSET • Jan. 1st, 1 A.D.  Dec. 31st, 9999 23:59:59.9999999 +- 14:00 • Precision : like others, 100 ns (7 decimal places) • Storage : 8 – 10 bytes, depending on precision • New system functions / arguments: • SYSDATETIMEOFFSET() • SWITCHOFFSET() • TODATETIMEOFFSET() • DATENAME(TZOffset, <value>) • Now we can centrally store local time information and still report using UTC, without any elaborate schema changes • I don’t think this feature is polished just yet • DEMO

  9. MERGE • UPSERT and then some (also supports DELETE) • Allows you to process multiple changes (e.g. from a staging table) in a single statement • Syntax is quite cumbersome:MERGE <target> USING <source> ON (<join condition>) WHEN MATCHED [AND…] THEN… WHEN NOT MATCHED THEN… WHEN SOURCE NOT MATCHED THEN… • Be careful with @@ROWCOUNT in your triggers! • (create individual triggers, or check inserted/deleted) • DEMO

  10. MERGE (continued) • PLEASE, PLEASE, PLEASE read Books Online before attempting to use MERGE. It is a very powerful statement, and as such, has the potential to be very dangerous.

  11. MERGE (continued) • MERGE can be dangerous!

  12. CHANGE DATA CAPTURE • Captures table modifications … insert, update, delete • Stores the information in a relational format, usable in auditing and/or replicating changes to a different table • Must enable at database (sysadmin) then table (dbo) • You can optionally select only a subset of columns to track (e.g. omit LOB) • DEMO

  13. CHANGE DATA CAPTURE • Operations captured in __$operation column : • 1 = delete • 2 = insert • 3 = update before (equivalent to deleted pseudo-table) • 4 = update after (equivalent to inserted pseudo-table) • 5 = merge • However, UPDATE operations through MERGE appear as independent DELETE/INSERT pairs • I filed a bug on this:https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=294625

  14. CHANGE DATA CAPTURE • CDC does not capture enough auditing data: • SPID • HOSTNAME • USERNAME • CURRENT_TIMESTAMP • Here is a sample trigger to augment the data:http://sqlblog.com/blogs/aaron_bertrand/archive/2007/06/21/playing-with-cdc-in-katmai.aspx • And here is a request on Connect:https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283707

  15. TABLE-VALUED PARAMETERS • Finally getting array-like functionality in T-SQL • Use table type to pass a table (by reference) to a function or a stored procedure • Better option for some BULK INSERT scenarios • Restrictions (similar to table variables): • Statistics are not maintained • Table becomes READONLY when passed • Cannot be target of SELECT INTO or INSERT EXEC • Cannot change or drop type if any object references it • DEMO

  16. DECLARATIVE MANAGEMENT FRAMEWORK • Policy-based infrastructure to manage SQL Server • Naming conventions, surface area configuration, state of features (database mail, xp_cmdshell) • Apply policies on one instance, export to XML, and quickly apply the same policies across the enterprise • Can be used in a mix of audit and enforcement modes • Check on change (uses Event Notifications) • Check on schedule (uses SQL Server Agent) • Enforce (uses DDL triggers to prevent violations) • Enforce requires transactional events (e.g. not CREATE DATABASE) • Current CTPs only have GUI and little exposed programmability (though you can figure it out with Profiler, later CTPs will be more complete) • Requires PolicyAdministratorRole in msdb(sysadmin works too)

  17. GROUPING SETS • New GROUP BY option with OLAP-like capabilities • Can replace or augment WITH CUBE, WITH ROLLUP • Can replace UNION ALL when combining multiple GROUP BY queries… GROUP BY GROUPING SETS ( (Column1, Column2), Column3, () -- all elements ), Column1, Column3; • Very long and detailed topic in Katmai BOL, current URL:ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/86f5366c-0e2c-4792-9cb6-5bad970c76d2.htm

  18. PERFORMANCE WAREHOUSE • Powerful repository for collecting data (not just performance metrics, as the name implies) • New user database called MDW (management data warehouse), installed by default • SSMS allows you to point/click to configure collection • In CTP4, only “Disk Usage” collection set is provided • More collection sets are coming; or create your own… • Reporting is not yet complete • Managed API is forthcoming

  19. HierarchyID • New data type to store/describe hierarchical information • Less complex than typical hierarchy schemes, but still requires external programming and thought to create correct tree • Very compact storage compared to exploded path • Less readable, but makes columns like ManagerID redundant • Choose your own performance : you can create the type of index for your queries (level, tree, node) • Many methods (e.g. ToString(), GetLevel(), Read())… beware potential name conflicts when upgrading SQL 2005 with existing CLR assemblies • Not a global solution - many cases where HierarchyID is NOT the best choice for representing hierarchical data

  20. … other changes … • More T-SQL replacing system procedures (e.g. ALTER DATABASE SET COMPATIBILITY_LEVEL vs. sp_dbcmptlvlabcxyz) • More data compression (sparse columns, think vardecimal) • Backup compression (applause!?) • Resource governor • FileStream (store LOB on disk; transactional control) • Spatial support (geo / mapping applications) • New table / query hints (e.g. FORCESEEK) • Row constructors • Enhancements in trace, mirroring, clustering, procedure cache • Hot-add CPU • CLR types / aggregates > 8K • LINQ support • Entity framework support (actually a .NET feature) • Potentially watch for report enrichment (Dundas acquisition) • Other items I cannot disclose yet (sorry, NDA)

  21. THE END • Questions?aaron.bertrand@cox.net • Please use Connect to vote / suggest / complain:http://connect.microsoft.com/SQLServer/

  22. CSSUG Follow Up Email survey Thoughts on the virtual format Suggestions for meeting topics Suggestions for future speakers Check the web Site for meeting updates www.Charlotte-SQL.org Thanks

More Related