SQL Server 2008 Overview “Katmai”

1. SQL Server 2008 Overview“Katmai” Don Vilen Don.Vilen@microsoft.com Program Manager SQL Server

2. Session Overview • Goals • Dive into what’s new for SQL Server 2008 • Code and programming examples if we have time • See some of SQL Server 2008 in action • June CTP now available!!! • Download at microsoft.com/sql • Whitepaper and webcast also • Currently looking for early adopters in Canada • Talk with your account rep about joining the TAP program

3. Your Data Any Place, Any Time Enterprise Data Platform Services Reporting Integration Analysis Synch Entity Data Model Dynamic Development Query Search FILE RDBMS OLAP XML Beyond Relational Mobile and Desktop Server Cloud Pervasive Insight

4. Entity Aware Adapters • SQL Server Change Tracking • Synchronized Programming Model • Visual Studio Support • SQL Server Conflict Detection • FILESTREAM data attribute • Integrated Full Text Search • Sparse Columns • Large User Defined Types • Date/Time Data Type • LOCATION data type • SPATIAL data type • Virtual Earth Integration • Partitioned Table Parallelism • Query Optimizations • Persistent Lookups • Change Data Capture • Backup Compression • MERGE SQL Statement • Data Profiling • Star Join • Enterprise Reporting Engine • Internet Report Deployment • Block Computations • Scale out Analysis • BI Platform Management • Export to Word and Excel • Author reports in Word and Excel • Report Builder Enhancements • TABLIX • Rich Formatted Data • Personalized Perspectives • … and many more • Transparent Data Encryption • External Key Management • Data Auditing • Pluggable CPU • Transparent Client Redirect for Database Mirroring • Declarative Management Framework • Server Group Management • Streamlined Installation • Enterprise System Management • Performance Data Collection • System Analysis • Data Compression • Query Optimization Modes • Resource Governor • Entity Data Model • LINQ • Visual Entity Designer

5. Delivering On Our Vision Enterprise Data Platform Secure, trusted platform for your data Optimized and predictable system performance Productive policy-based management of your infrastructure Enterprise Data Platform Beyond Relational • Store and consume any type of data • Deliver Location Intelligence within your applications Dynamic Development Dynamic Development Accelerate your development with entities Synchronize your data from anywhere Beyond Relational Pervasive Insight Pervasive Insight Integrate all your data in the Enterprise Data Warehouse Reach all your users with scalable BI platform Empower every user with actionable insights

6. Transparent Data Encryption Secure, Trusted Platformfor Your Data Enterprise Data Platform • External Key Management • Protect your information • Encrypt your data without requiring an application rewrite • Backup your data securely • Consolidate security keys within the data center • Answer common auditing questions, like “What data was retrieved?” • Increase the reliability of your applications • Add system resources without affecting your users • Add automatic failover transparency to your existing applications • Data Auditing • Hot Add CPU • Failover Transparency • Automatic Page Repair • Backup Compression

7. Automatic Page Repairwith Database Mirroring

8. Spend less time on ongoing operations Manage via policies instead of scripts Define enterprise-wide data management policies Automated monitoring and enforcement of policies Simplify your installation and configuration Integrated with your enterprise system management Define Policies that are compliant with System Definition Model Manage your data and system infrastructure with Microsoft System Center • Declarative Management Framework Productive Policy-based Management of Your Infrastructure Enterprise Data Platform • Server Group Management • Streamlined Installation • Enterprise System Management

9. Performance Data Collection Optimized and Predictable Performance Enterprise Data Platform • System Analysis • Data Compression • Optimize your system performance • Monitor your system by collecting and storing system events • Analyze and report on your system performance • Reduce your storage costs and increase query performance • Predictable performance • Manage concurrent workloads on single system • Prevent runaway queries • Ensure consistent query performance • Query Optimization Modes • Resource Governor

10. Database Engine SQL Server 2005 Resource Management SQL Server • Single resource pool • Database engine doesn’t differentiate workloads • Best effort resource sharing Backup OLTP Activity Admin Tasks Executive Reports Ad-hoc Reports Workloads Memory, CPU, Threads, … Resources

11. Database Engine Resource Governor – Workloads SQL Server • Ability to differentiate workloads • e.g. app_name, login • Per-request limits • Max memory % • Max CPU time • Grant timeout • Max Requests • Resource monitoring Executive Reports Backup OLTP Activity Admin Tasks Ad-hoc Reports Report Workload OLTP Workload Admin Workload Memory, CPU, Threads, … Resources

12. Database Engine Resource Governor – Importance SQL Server • A workload can have an importance label • Low • Medium • High • Gives resource allocation preference to workloads based on importance Executive Reports Backup OLTP Activity Admin Tasks Ad-hoc Reports High Report Workload OLTP Workload Admin Workload Memory, CPU, Threads, … Resources

13. Database Engine Resource Governor – Pools SQL Server • Resource pool: A virtual subset of physical database engine resources • Provides controls to specify • Min Memory % • Max Memory % • Min CPU % • Max CPU % • Max DOP • Resource monitoring • Up to 20 resource pools Executive Reports Backup OLTP Activity Admin Tasks Ad-hoc Reports High Report Workload OLTP Workload Admin Workload Max CPU 90% Min Memory 10% Max Memory 20% Max CPU 20% Application Pool Admin Pool

14. Database Engine Resource Governor SQL Server Putting it all together • Workloads are mapped to Resource Pools (n : 1) • Online changes of groups/pools • SQL Server 2005, others • ‘default’ group • ‘default’ pool Main Benefit • Prevent run-away queries Executive Reports Backup OLTP Activity Admin Tasks Ad-hoc Reports High Report Workload OLTP Workload Admin Workload Max CPU 90% Min Memory 10% Max Memory 20% Max CPU 20% Application Pool Admin Pool

15. Delivering On Our Vision Enterprise Data Platform Secure, trusted platform for your data Productive policy-based management of your infrastructure Optimized and predictable system performance Dynamic Development Accelerate your development with entities Synchronize your data from anywhere Pervasive Insight Integrate all your data in the Enterprise Data Warehouse Reach all your users with scalable BI platform Empower every user with actionable insights Beyond Relational Store and consume any type of data Deliver Location Intelligence within your applications

16. Rich Information Services InformationWorker Developer Search + Query Info Mgmt Sync Entities Enabling the development of business-centric and location-aware applications Customers . Orders Improving search, analysis, and collaboration across traditional data & documents XML Data Spatial Data Relational Data Docs, pictures, video and music Integrated Storage Platform Lowering TCO thru lower storage and management cost IT Professionals Integrating the Data Experience

17. Entity Data Model Accelerate Your Development with Entities Dynamic Development • LINQ – Language Independent Query • Develop with business entities • Define your business not tables • Model your complex business relationships • Retrieve entities instead of rows and columns • Connect to SQL Server with entity aware adapters • Reliable application performance • Store entities locally on your clients • Visual Entity Designer • Entity Aware Adapters

18. SQL Server Change Tracking Connect to Your Data from Any Device Dynamic Development • Synchronized Programming Model • Access your data from anywhere • Store your data locally while disconnected from server • Synchronize incremental changes between client and server • Detect conflicts during synchronization including deletes • Add disconnected scenarios without rewriting existing applications • Visual Studio Support • SQL Server Conflict Detection

19. Delivering On Our Vision Enterprise Data Platform Secure, trusted platform for your data Productive policy-based management of your infrastructure Optimized and predictable system performance Dynamic Development Accelerate your development with entities Synchronize your data from anywhere Pervasive Insight Integrate all your data in the Enterprise Data Warehouse Reach all your users with scalable BI platform Empower every user with actionable insights Beyond Relational Store and consume any type of data Deliver Location Intelligence within your applications

20. FILESTREAM data storage attribute Store and Consume Any Type of Data Beyond Relational • Integrated Full-Text Search • Seamless transition between relational and non-relational • Store your documents in cost effective storage platform • Access documents as data • Encode your complex hierarchies within XML • Extend the database to meet your unique needs • Find your data • Query across relational and text data • Build global applications • Capture precise time from clients across the globe • Sparse Columns and Filtered Indexes • Large User Defined Types • Hierarchical Data Types • Date and TimeData Types

21. GEOMETRY – Location data type Deliver Location Intelligence Within Your applications Beyond Relational • GEOGRAPHY – Spatial data type • Build location-aware applications • Capture location data from across your organization • Integrate location intelligence to your existing applications • Visualize your location information • Standard Spatial applications • Integrate with industry standard spatial applications • Supported by third parties • Virtual Earth Integration

22. Delivering On Our Vision Enterprise Data Platform Secure, trusted platform for your data Productive policy-based management of your infrastructure Optimized and predictable system performance Dynamic Development Accelerate your development with entities Synchronize your data from anywhere Pervasive Insight Integrate all your data in the Enterprise Data Warehouse Reach all your users with scalable BI platform Empower every user with actionable insights Beyond Relational Store and consume any type of data Deliver Location Intelligence within your applications

23. Enhanced Partitioning Parallelism Enterprise Data Warehouse Pervasive Insight • Enhanced DW Query Optimizations • Scale and Manage large number of users and data • Improve Query performance on large tables • Optimize Queries for data warehousing scenarios • Increase I/O performance with efficient and cost effective data storage • Manage concurrent workloads of ad-hoc queries, reporting and analysis • Integrate growing volumes of data • Optimize ETL performance by identifying data in your largest tables • Reduce the data load volumes by capturing operational changes in data • Simplify the insert and update data processing • Profile your information to identify dirty data • Data Compression, Resource Governor, Change Data Capture • MERGE T-SQL Statement • Data Profiling • Persistent Lookups • Minimal Logging

24. Deliver insights throughout your organization Deliver reports of any size at enterprise scale Scale out through read-only Analysis Services storage Enhance analytical capabilities with more complex computations and aggregations Deploy and manage your BI infrastructure Streamline development of the analysis infrastructure Backup cubes with enhanced scalability Improve productivity with new cube design tools Optimize cube design with real time best practice alerts Deploy Reporting Services without IIS dependency Reach All your Users withScalable BI platform Pervasive Insight

25. Empower Every UserWith Actionable Insight Pervasive Insight • Deliver information via Microsoft Office • Render reports to Microsoft Word • Enjoy improved rendering to Microsoft Excel • Enable users to create powerful reports • Build powerful ad-hoc reports • Create reports with any structure using Tablix • Add rich text regions your reports • Embed powerful graphical data visualizations into reports (Dundas) • Empower users with enhanced analysis • Empower users with enhanced write-back scenarios • Enhanced Data Mining structures and bring data mining to a new, broader audience through Office

26. There's much more that we're adding … and some that we’re taking out • DB Engine • Partition-aligned indexed views • Locking enhancements – escalate to partition lock • Analysis Services • Query Tracing – like Showplan, but for AS • Write-back performance • Resource monitoring – like the DMVs in the engine • Integration Services • Visual Studio Tools for Applications (VSTA) scripting

27. Deprecation • Getting more serious about deprecation policy • Deprecation announcements since SQL Server 7.0 • Formalized policy in SQL Server 2005 • Deprecation stages – over 3 major releases • Announcement – typically only once an alternative exists • Final support – will be removed in next major version • SQL Server 2008 implements the deprecation policy • Perf counters for all deprecated features • Trace events to track deprecated feature usage • DEPRECATION_ANNOUNCEMENT • DEPRECATATION_FINALSUPPORT • Books Online, Upgrade Advisor

28. Deprecation and SQL Server 2008 • Items removed in SQL Server 2008 • sp_addalias • Backup and restore • DUMP statement • LOAD statement • BACKUP LOG WITH NO_LOG • BACKUP LOG WITH TRUNCATE_ONLY • BACKUP TRANSACTION • sp_helpdevice • 60, 65, and 70 compatibility levels • DBCC CONCURRENCYVIOLATION • sp_addgroup, sp_changegroup • sp_dropgroup, sp_helpgroup • Northwind and pubs sample databases

29. Delivering On Our Vision Enterprise Data Platform Secure, trusted platform for your data Optimized and predictable system performance Productive policy-based management of your infrastructure Enterprise Data Platform Beyond Relational • Store and consume any type of data • Deliver Location Intelligence within your applications Dynamic Development Dynamic Development Accelerate your development with entities Synchronize your data from anywhere Beyond Relational Pervasive Insight Pervasive Insight Integrate all your data in the Enterprise Data Warehouse Reach all your users with scalable BI platform Empower every user with actionable insights

30. Your Data Any Place, Any Time Enterprise Data Platform Services Reporting Integration Analysis Synch Entity Data Model Dynamic Development Query Search FILE RDBMS OLAP XML Beyond Relational Mobile and Desktop Server Cloud Pervasive Insight

31. Call to Action Visit http://connect.microsoft.com/sql Submit suggestions and bugs; they go directly into SQL Dev databases for tracking, feedback, follow-up Download the latest SQL Server “Katmai” CTP from: http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx SQL Server TechCenter: http://www.microsoft.com/technet/prodtechnol/sql/default.mspx SQL Server DevCenter:http://msdn.microsoft.com/sql SQL Server Best Practices:http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx

32. Resources Technical Communities, Webcasts, Blogs, Chats & User Groups http://www.microsoft.com/communities/default.mspx Microsoft Learning and Certification http://www.microsoft.com/learning/default.mspx Microsoft Developer Network (MSDN) & TechNet http://microsoft.com/msdn http://microsoft.com/technet Trial Software and Virtual Labs http://www.microsoft.com/technet/downloads/trials/default.mspx

33. For the Developers !! If we still have some time ..

34. New Transact-SQL System Data Types • Date and Time types • Table Type and Table Valued Parameters • HierarchyID type • Sparse Columns • Filtered Indexes and Statistics

35. Date and Time Data Types Date Time DateTimeOffset DateTime2 • Date only • From 0001-01-01 to 9999-01-01 in Gregorian calendar • Time only • Optional user specifiable fractional precisions up to 100 nanoseconds • Time-zone aware/preserved UTC datetime • Optional user specifiable fractional precisions up to 100 nanoseconds • Large date range; not time-zone aware • Optional user specifiable fractional precisions up to 100 nanoseconds

36. Date and Time Data Types CREATE TABLE t1 (c1 DATE, c2 TIME(3), c3 DATETIME2(7) NOT NULL DEFAULT GETDATE(), c4 DATETIMEOFFSET CHECK (c4 < CAST(GETDATE() AS DATETIMEOFFSET(0))) ); INSERT INTO t1 VALUES ('0001-01-01', '23:59:59', '0001-12-21 23:59:59.1234567', '0001-10-21 23:59:59.1234567 -07:00'); INSERT INTO t1 VALUES ('9999-12-31', '23:59:59', '9999-12-31 23:59:59.1234567', '1111-10-21 23:59:59.1234567 -07:00'); SELECT c4, DATEPART( TZOFFSET, c4 ), DATEPART( ISO_WEEK, c4 ), DATEPART( MICROSECOND, c4 ) FROM t1;

37. Table-valued Parameters Client side Programming Support User-defined Table Types Table-valued Parameters (TVP) • A new user defined type – aligned with table variable definition • Enhancements to “Create Type” syntax • CREATE TYPE myTableType AS TABLE (id INT, name NVARCHAR(100),qty INT); • Used for table variable or SP/function parameter declarations only • Parameters of ‘Table Type’ for SPs and Functions • CREATE PROCEDURE myProc (@tvpmyTableType READONLY) AS … • Backed by temp tables at the server • TVPs are scoped within the SP/Function body • Input Parameters only – can not be modified inside the SP body • Fully supported by OLEDB/ODBC/ADO.NET client stacks • Efficient transport implementation in TDS

38. Table-valued Parameters -- create a user TABLE type CREATE TYPE myTableType AS TABLE (id INT, name NVARCHAR(100), qty INT); -- declare & populate variable of the TABLE type DECLARE @list AS myTableType; INSERT into @list VALUES (1, ‘Bicycle’, 10), (2, ‘Roller blades’, 5), (3, ‘Soccer ball’, 25); -- Pass table variable as a parameter to a stored procedure CREATE PROCEDURE myProc (@tvpmyTableType READONLY) AS UPDATE Inventory SET qty += s.qty FROM Inventory AS i INNER JOIN @tvp AS tvp ON i.id = tvp.id GO EXEC myProc @list;

39. Sparse Columns • “Sparse”: a storage attribute on a column Create Table Products(Id int, Resolution nvarchar(8), ZoomLength nvarchar(8) SPARSE); • Storage Optimization: • 0 bytes stored for a NULL value • Co-location of data • No change in Query/DML behavior • NULL Compression in the TDS layer • Metadata flag to identify a sparse column

40. Sparse Columns • Sparse Column Set • A logical grouping for all sparse columns in a table Create Table Products(Id int, Type nvarchar(16), ProductProperties XML COLUMN_SET FOR ALL_SPARSE_COLUMNS); • Updateable, computed XML column ‘<Resolution>6 mb</Resolution> <ZoomLength>3x</ZoomLength>’ • Select * returns all non-sparse-columns, sparse column set • Allows generic retrieval/update of sparse columns as a set • Wide Tables • Large number of sparse columns allowed in a table • 100,000 Columns, 3000 indexes • Limited number of non-null values in any record • Requires a sparse column set when > 1024 columns • Limits on non-sparse columns(1024), result set(4096) stay

41. Filtered Indexes and Statistics • Index Definition: • Additional where clause in index definition Create Index Ix on Products(ZoomLength) where Type=‘Camera’; • Filtered/co-related statistics creation and usage • Filtered statistics can also be created independently Create statistics stx on Products(WaistLength) where Type=‘Pant’; • Query/DML Optimization to use Filtered indexes & Statistics • Restrictions • Restricted grammar set for the filtering expression • Only on non-clustered indexes

42. Transact-SQL Enhancements • MERGE Statement • INSERT over DML • Object Dependencies • Large UDTs • SQL/CLR Enhancements • Grouping Sets • DDL Trigger Enhancements • Delighters... • Deprecation • Beyond Relational

43. MERGE statement • New DML statement (aka “UPSERT”) • Perform multiple DML operations against a single target table based on a source table • INSERT / UPDATE / DELETE • SQL-2006 compliant (with extensions) • Uses: • OLTP insert-or-update (upsert) • Synchronize target with source (insert/update/delete) • … and more! • Fully orthogonal • CTEs, TOP, OUTPUT, OPTION, …

44. Available in CTP Database Engine MERGE MERGE Stock S USING Trades T ON S.Stock = T.Stock WHEN MATCHED AND (Qty + Delta = 0) THEN DELETE -- delete stock if Qty reaches 0 WHEN MATCHED THEN -- delete takes precedence on update UPDATE SET Qty += Delta WHEN NOT MATCHED THEN INSERT VALUES (Stock, Delta) OUTPUT $action, T.Stock, inserted.Delta;

45. Available in CTP Database Engine INSERT over DML • Ability to have INSERT statement consume results of DML • Enhancement over OUTPUT INTO <table> clause • DML OUTPUT can be filtered with a WHERE clause • Data accessing predicates not allowed (sub-queries, data accessing UDFs and full-text) • Why? • History tracking of slowly changing dimensions • Dumping DML data stream to a secondary table for post-processing

46. Available in CTP Database Engine INSERT over DML INSERT INTO Books (ISBN, Price, Shelf, EndValidDate) SELECT ISBN, Price, Shelf, GetDate() FROM ( MERGE Books T USING WeeklyChanges AS S ON T.ISBN = S.ISBN AND T.EndValidDate IS NULL WHEN MATCHED AND (T.Price <> S.Price OR T.Shelf <> S.Shelf) THEN UPDATE SET Price = S.Price, Shelf = S.Shelf WHEN NOT MATCHED THEN INSERT VALUES(S.ISBN, S.Price, S.Shelf, NULL) OUTPUT $action, S.ISBN, Deleted.Price, Deleted.Shelf ) Changes(Action, ISBN, Price, Shelf) WHERE Action = 'UPDATE’;

47. Object Dependencies • Reliable discovery of dependencies between objects • Stored procedures, tables, views, functions, triggers, UDTs, etc… • Schema-bound & non-schema-bound objects • Common Uses: • Discover all objects that a given object depends on • Discover all objects that depend on a given object • Discover all objects that depend on another database • Discover all objects performing distributed queries using four-part names

48. Object Dependencies sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities • New catalog view; replaces sys.sql_dependencies • Tracks both schema-bound and non-schema-bound dependencies • Tracks cross-database and cross-server references (by name) • New dynamic management function; replaces sp_depends • Returns a row for each entity referenced by a given entity • E.g.: show me all objects referenced in stored procedure p1 • New dynamic management function; replaces sp_depends • Returns a row for each entity that references a given entity • E.g.: show me all objects that would be broken if I drop table t1

49. Object Dependencies CREATE PROCEDURE dbo.p2 SELECT * FROM t1; ... CREATE PROCEDURE p3 UPDATE dbo.t1 ... CREATE VIEW v1 WITH SCHEMABINDING SELECT t1.*, t2.* FROM dbo.t1 INNER JOIN dbo.t2 ... CREATE FUNCTION s.foo (@x INT) RETURNS TABLE AS BEGIN SELECT * FROM t1 WHERE a < @x; END EXEC sys.dm_sql_referencing_entities (‘dbo.t1’);

50. Large UDTs • SQL Server 2005 • User-Defined Types limited to 8K bytes • Fine for most objects, but some spatial objects could be quite large • SQL Server 2008 • UDTs can be of max LOB size • Conceptually identical to varbinary(max) • The size of a UDT is defined on the type, and will be reflected as -1 in type metadata • Converted to varbinary(max) or image for down-level clients