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

Loading in 2 Seconds...

play fullscreen
1 / 39

SQL Server 2008 - PowerPoint PPT Presentation


  • 173 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Server 2008' - libitha


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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
Agenda
  • About SQL Server 2008
  • Enhancements in SSMS and T-SQL Syntax
  • T-SQL improvements and data types
  • Tracking Changing Data
  • Partitioning Enhancements
agenda3
Agenda

3

  • About SQL Server 2008
  • Enhancements in SSMS and T-SQL Syntax
  • T-SQL improvements and data types
  • Tracking Changing Data
  • Partitioning Enhancements
enhancements in ssms and t sql syntax
Enhancements in SSMS and T-SQL Syntax
  • Editor enhancements (indentation, collapsing)
  • T-SQL IntelliSense
  • T-SQL Debugger
  • Code abbreviations

5

slide6
Demo

6

  • Enhancements in SSMS and T-SQL Syntax
agenda7
Agenda

7

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

8

  • MERGE statement
  • Table-Valued Parameters
  • Grouping Sets
  • New Date and Time Data Types
merge statement merging data
MERGE Statement: Merging Data

Delete

Deleted

Update

Updated

Insert

New

Source

Merged Data

Target

9

What is to merge data?

merge statement
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:

slide11

Demo

  • MERGE statement
  • MERGE vs. “UPSERT”

11

table valued parameters
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

using table value parameters
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
slide14

Demo

  • Table-Valued Parameters vs. old alternatives

14

tvp implementation and performance
TVP Implementation and Performance

15

  • Table Variables materialized in TEMPDB

Faster than parameter arrays, BCP APIs still fastest

tvp in net
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
grouping sets
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

more on grouping sets
More on Grouping Sets

18

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

Demo

  • GROUPING SETS
  • GROUPING and GROUPING_ID Functions

19

new date and time data types
New Date and Time Data Types

Prev. SQL Server:

DATETIME

TIME

DATE

SQL Server 2008:

20

20

date and time
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

2005 time date alternatives
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

datetime2 and datetimeoffset
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

date time library extensions
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

slide26
Demo

26

  • New date and time data types
  • New data and time functions
agenda27
Agenda
  • About SQL Server 2008
  • Enhancements in SSMS and T-SQL Syntax
  • T-SQL improvements and data types
  • Tracking Changing Data
  • Partitioning Enhancements

27

tracking changing data
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

change data capture
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
slide32

Demo

  • Change Tracking
  • Change Data Capture

32

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

33

partitioning enhancements
Partitioning Enhancements

34

  • Partition-Aligned Indexed Views
  • Date-Only Data type
  • Partitioned Table Parallelism
  • Partition-Level Lock Escalation
lock escalation the problem
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
lock escalation the solution
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
slide37

Demo

  • Partitioning Enhancements:
    • Partition-Level Lock Escalation

37

learn more
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
thank you

Thank You!

Aviel Iluz | Database Consultant

Email: avieli@srl.co.il