1 / 39

SQL Server 2008

SQL Server 2008. What’s New for Developers. Speaker: Aviel Iluz | Database Consultant Contact: avieli@srl.co.il , 052-3134185 SRL DBA Team Blog: http://blogs.microsoft.co.il/blogs/srldba/. Agenda. About SQL Server 2008 Enhancements in SSMS and T-SQL Syntax

libitha
Download Presentation

SQL Server 2008

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 2008 What’s New for Developers Speaker: Aviel Iluz | Database Consultant Contact:avieli@srl.co.il, 052-3134185 SRL DBA Team Blog: http://blogs.microsoft.co.il/blogs/srldba/

  2. Agenda • About SQL Server 2008 • Enhancements in SSMS and T-SQL Syntax • T-SQL improvements and data types • Tracking Changing Data • Partitioning Enhancements

  3. Agenda 3 • About SQL Server 2008 • Enhancements in SSMS and T-SQL Syntax • T-SQL improvements and data types • Tracking Changing Data • Partitioning Enhancements

  4. SQL Server 2008 Vision 4

  5. Enhancements in SSMS and T-SQL Syntax • Editor enhancements (indentation, collapsing) • T-SQL IntelliSense • T-SQL Debugger • Code abbreviations 5

  6. Demo 6 • Enhancements in SSMS and T-SQL Syntax

  7. Agenda 7 • About SQL Server 2008 • Enhancements in SSMS and T-SQL Syntax • T-SQL improvements and data types • Tracking Changing Data • Partitioning Enhancements

  8. T-SQL improvements and data types 8 • MERGE statement • Table-Valued Parameters • Grouping Sets • New Date and Time Data Types

  9. MERGE Statement: Merging Data Delete Deleted Update Updated Insert New Source Merged Data Target 9 What is to merge data?

  10. MERGE Statement SQL Server 2008: A Single DML Statement BEGIN TRAN COMMIT MERGE t USING s ON t.ID = s.ID WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT WHEN NOT MATCHED BY SOURCE THEN DELETE; UPDATE t INNER JOIN s INSERT t LEFT OUTER JOIN DELETE t RIGHT JOIN s 10 SQL Server 2005: Multiple DML Statements:

  11. Demo • MERGE statement • MERGE vs. “UPSERT” 11

  12. Table-Valued Parameters Parsing string of delimited values Table Value Parameter @p ='1,2,3,4,5,…' Shredding XML temp table outside the SP SQL Server 2005 SQL Server 2008 12 Common challenge: Passing list of values to SP/FN Problem: No ARRAY data type

  13. Using Table Value Parameters CREATE TYPE mytab AS TABLE (id int); DECLARE @t mytab; CREATE PROC dbo.usp_usetable (@list AS mytab READONLY) DECLARE @t mytab; INSERT @t VALUES (1), (2), (3); EXEC dbo.usp_usetable @list = @t 13 • Create strongly typed table variable (new!) • Use as a parameter (must be READONLY) • Declare and initialize TABLE variable

  14. Demo • Table-Valued Parameters vs. old alternatives 14

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

  16. TVP in .NET // Create a data table, and provide its structure DataTable customerTable = new DataTable(); customerTable.Columns.Add("Name", typeof(string)); customerTable.Columns.Add("City", typeof(string)); customerTable.Columns.Add("Phone", typeof(string)); // Fill with rows using (SqlConnection conn = new SqlConnection("...")) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "dbo.CustomersInsertMany"; SqlParameter param = cmd.Parameters.AddWithValue("@CustomersTable", customerTable); conn.Open(); cmd.ExecuteNonQuery(); } 16 • TVP Passed From the APP

  17. Grouping Sets Common challenge: Many grouping sub-totals required from the same table SQL Server 2005 SQL Server 2008 SELECT a, sum(q) FROM T GROUP BY a UNION ALL SELECT a, b, sum(q) FROM T GROUP BY a, b UNION ALL SELECT a, b, c, sum(q) FROM T GROUP BY a, b, c SELECT a, b, c, sum(q) FROM T GROUP BY GROUPING SETS ( (a), (b), (a, b, c) ) 17

  18. More on Grouping Sets 18 • Single read pass for performance • GROUPING_ID() and GROUPING() new function • Distinguish between different subtotals

  19. Demo • GROUPING SETS • GROUPING and GROUPING_ID Functions 19

  20. New Date and Time Data Types Prev. SQL Server: DATETIME TIME DATE SQL Server 2008: 20 20

  21. DATE and TIME • DATE Data Type • Date Only • Large range: 01-01-0001 to 31-12-9999 • TIME Data Type • Time Only • Variable Accuracy: up yo 100 21

  22. 2005 TIME/DATE alternatives • SQL Server 2005 Alternatives to TIME/DATE • User Defined Data Types + Rules • Creating Computed Columns • Extracting time/date component form DATETIME 22 22

  23. DATETIME2 and DATETIMEOFFSET • DATETIME2 Data Type • Large range (like DATE) • High precision (like TIME) • DATETIMEOFFSET • Like DATETIME2 • + Time Zone Offset • YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 23

  24. New Date and Time Data Types – Summary Table 24

  25. Date Time Library Extensions • Higher precision current date/time uses • SYSDATETIME • SYSUTCDATETIME • SYSDATETIMEOFFSET • Special functions for DATETIMEOFFSET • SWITCHOFFSET (datetimeoffset, timezone) • TODATETIMEOFFSET (any date/time, timezone) 25

  26. Demo 26 • New date and time data types • New data and time functions

  27. Agenda • About SQL Server 2008 • Enhancements in SSMS and T-SQL Syntax • T-SQL improvements and data types • Tracking Changing Data • Partitioning Enhancements 27

  28. Tracking Changing Data Timestamp column Change Tracking (synchronous) Change Data Capture (asynchronous) Triggers and schema changes Common challenge: Track data changes for data storage synchronisation SQL Server 2005 SQL Server 2008 28

  29. Change Data Capture 29 • SQL Agent jobs periodically (asynchronously) scan the transaction log for change data • Change data is placed in change relational tables • Changes are requested using TVFs

  30. CDC vs. Change Tracking (1) CDC CT 30

  31. CDC vs. Change Tracking (2) 31

  32. Demo • Change Tracking • Change Data Capture 32

  33. Agenda • About SQL Server 2008 • Enhancements in SSMS and T-SQL Syntax • T-SQL improvements and data types • Tracking Changing Data • Partitioning Enhancements 33

  34. Partitioning Enhancements 34 • Partition-Aligned Indexed Views • Date-Only Data type • Partitioned Table Parallelism • Partition-Level Lock Escalation

  35. Lock Escalation: The Problem Query 1 Query 2 Partitioned Table IX X ESCALATE update update Partition 2 Partition 1 Partition 3 ` FG2 FG1 FG3 35 • Lock escalation on partitioned tables locks ALL partitions • Only way to solve this currently is to disable escalation

  36. Lock Escalation: The Solution Query 1 Query 2 Partitioned Table IX ESCALATE update update X Partition 2 Partition 1 Partition 3 FG1 FG2 FG3 36 • SQL Server 2008 allows lock escalation to the partition level • Escalation to partition level does not block queries on other partitions

  37. Demo • Partitioning Enhancements: • Partition-Level Lock Escalation 37

  38. Learn More • • Itzik Ben-Gan, Introduction to New T-SQL Programmability Features in SQL Server 2008 • http://msdn.microsoft.com/en-gb/library/cc721270(SQL.100).aspx • • MSDN Webcast: New T-SQL Programmability Features in SQL Server 2008 • http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032357754&CountryCode=US

  39. Thank You! Aviel Iluz | Database Consultant Email: avieli@srl.co.il

More Related