SQL Server 2008 - PowerPoint PPT Presentation

sql server 2008 l.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server 2008 PowerPoint Presentation
SQL Server 2008

play fullscreen
1 / 39
Download Presentation
arama
Views
Download Presentation

SQL Server 2008

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