SQL Server 2008

1
SQL Server 2008

• Whats New for Developers

Speaker Aviel Iluz Database Consultant Contact
avieli_at_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

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

3
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

• Enhancements in SSMS and T-SQL Syntax

6
7
Agenda

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

7
8
T-SQL improvements and data types

• MERGE statement
• Table-Valued Parameters
• Grouping Sets
• New Date and Time Data Types

8
9
MERGE Statement Merging Data

• What is to merge data?

Merged Data
Source
Target
9
10
MERGE Statement

• SQL Server 2005
• Multiple DML Statements

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
11
Demo

• MERGE statement
• MERGE vs. UPSERT

11
12
Table-Valued Parameters

• Common challenge Passing list of values to
  SP/FN
• Problem No ARRAY data type

SQL Server 2005
SQL Server 2008
12
13
Using Table Value Parameters

• Create strongly typed table variable (new!)
• Use as a parameter (must be READONLY)
• Declare and initialize TABLE variable

CREATE PROC dbo.usp_usetable (_at_list AS mytab
READONLY)
DECLARE _at_t mytab INSERT _at_t VALUES (1), (2),
(3) EXEC dbo.usp_usetable _at_list _at_t
13
14
Demo

• Table-Valued Parameters vs. old alternatives

14
15
TVP Implementation and Performance

• Table Variables materialized in TEMPDB
• Faster than parameter arrays, BCP APIs still
  fastest

15
16
TVP in .NET

• TVP Passed From the APP

// 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.StoredPr
ocedure   cmd.CommandText "dbo.CustomersInsertM
any"   SqlParameter param cmd.Parameters.AddWit
hValue("_at_CustomersTable", customerTable)    
conn.Open()   cmd.ExecuteNonQuery()
16
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

• Single read pass for performance
• GROUPING_ID() and GROUPING() new function
• Distinguish between different subtotals

18
19
Demo

• GROUPING SETS
• GROUPING and GROUPING_ID Functions

19
20
New Date and Time Data Types
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 hhmmss.nnnnnnn -hhmm

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

• New date and time data types
• New data and time functions

26
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
Common challenge Track data changes for data
storage synchronisation
SQL Server 2005
SQL Server 2008
28
29
Change Data Capture

• 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

29
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

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

34
35
Lock Escalation The Problem

• Lock escalation on partitioned tables locks ALL
  partitions
• Only way to solve this currently is to disable
  escalation

35
36
Lock Escalation The Solution

• SQL Server 2008 allows lock escalation to the
  partition level
• Escalation to partition level does not block
  queries on other partitions

36
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/WebCastEventDet
  ails.aspx?cultureen-USEventID1032357754Country
  CodeUS

39
Thank You!
Aviel Iluz Database Consultant Email
avieli_at_srl.co.il