CSSUG

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
Todays 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
Whats New in Katmai(SQL Server 2008)
Aaron Bertrand SQL Server MVP aaron.bertrand_at_cox.n
et
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
• 000000.0000000 (midnight) ? 235959.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
  235959.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 wont consider changing
  the name)
• DEMO

8
DATETIMEOFFSET

• Jan. 1st, 1 A.D. ? Dec. 31st, 9999
  235959.9999999 - 1400
• Precision like others, 100 ns (7 decimal
  places)
• Storage 8 10 bytes, depending on precision
• New system functions / arguments
• SYSDATETIMEOFFSET()
• SWITCHOFFSET()
• TODATETIMEOFFSET()
• DATENAME(TZOffset, )
• Now we can centrally store local time information
  and still report using UTC, without any elaborate
  schema changes
• I dont 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
  USING ON () WHEN
  MATCHED AND THEN WHEN NOT MATCHED
  THEN WHEN SOURCE NOT MATCHED THEN
• Be careful with _at__at_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?Feedback
  ID294625

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/20
  07/06/21/playing-with-cdc-in-katmai.aspx
• And here is a request on Connect
  https//connect.microsoft.com/SQLServer/feedback/V
  iewFeedback.aspx?FeedbackID283707

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.v1
  0.en/s10de_1devconc/html/86f5366c-0e2c-4792-9cb6-5
  bad970c76d2.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_at_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