SQL Server 2008 .


36 views
Uploaded on:
Category: Funny / Jokes
Description
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
Transcripts
Slide 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/web journals/srldba/

Slide 2

Agenda About SQL Server 2008 Enhancements in SSMS and T-SQL Syntax T-SQL upgrades and information sorts Tracking Changing Data Partitioning Enhancements

Slide 3

Agenda 3 About SQL Server 2008 Enhancements in SSMS and T-SQL Syntax T-SQL upgrades and information sorts Tracking Changing Data Partitioning Enhancements

Slide 4

SQL Server 2008 Vision 4

Slide 5

Enhancements in SSMS and T-SQL Syntax Editor upgrades (space, falling) T-SQL IntelliSense T-SQL Debugger Code shortenings 5

Slide 6

Demo 6 Enhancements in SSMS and T-SQL Syntax

Slide 7

Agenda 7 About SQL Server 2008 Enhancements in SSMS and T-SQL Syntax T-SQL changes and information sorts Tracking Changing Data Partitioning Enhancements

Slide 8

T-SQL changes and information sorts 8 MERGE articulation Table-Valued Parameters Grouping Sets New Date and Time Data Types

Slide 9

MERGE Statement: Merging Data Delete Deleted Update Updated Insert New Source Merged Data Target 9 What is to consolidation information?

Slide 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:

Slide 11

Demo MERGE explanation MERGE versus "UPSERT" 11

Slide 12

Table-Valued Parameters Parsing string of delimited qualities Table Value Parameter @p = \'1,2,3,4,5 ,… " Shredding XML temp table outside the SP SQL Server 2005 SQL Server 2008 12 Common test: Passing rundown of qualities to SP/FN Problem : No ARRAY information sort

Slide 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 specifically table variable (new!) Use as a parameter (must be READONLY) Declare and instate TABLE variable

Slide 14

Demo Table-Valued Parameters versus old choices 14

Slide 15

TVP Implementation and Performance 15 Table Variables appeared in TEMPDB Faster than parameter exhibits, BCP APIs still quickest

Slide 16

TVP in .NET/Create an information table, and give 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 lines utilizing (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

Slide 17

Grouping Sets Common test : Many gathering sub-aggregates 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

Slide 18

More on Grouping Sets 18 Single read go for execution GROUPING_ID() and GROUPING() new capacity Distinguish between various subtotals

Slide 19

Demo GROUPING SETS GROUPING and GROUPING_ID Functions 19

Slide 20

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

Slide 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

Slide 22

2005 TIME/DATE choices SQL Server 2005 Alternatives to TIME/DATE User Defined Data Types + Rules Creating Computed Columns Extracting time/date segment frame DATETIME 22

Slide 23

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

Slide 24

New Date and Time Data Types – Summary Table 24

Slide 25

Date Time Library Extensions Higher accuracy current date/time utilizes SYSDATETIME SYSUTCDATETIME SYSDATETIMEOFFSET Special capacities for DATETIMEOFFSET SWITCHOFFSET (datetimeoffset, timezone) TODATETIMEOFFSET (any date/time, timezone ) 25

Slide 26

Demo 26 New date and time information sorts New information and time capacities

Slide 27

Agenda About SQL Server 2008 Enhancements in SSMS and T-SQL Syntax T-SQL upgrades and information sorts Tracking Changing Data Partitioning Enhancements 27

Slide 28

Tracking Changing Data Timestamp segment Change Tracking (synchronous) Change Data Capture (nonconcurrent) Triggers and composition changes Common test : Track information changes for information stockpiling synchronization SQL Server 2005 SQL Server 2008 28

Slide 29

Change Data Capture 29 SQL Agent occupations intermittently (nonconcurrently) examine the exchange log for change information Change information is put in change social tables Changes are asked for utilizing TVFs

Slide 30

CDC versus Change Tracking (1) CDC CT 30

Slide 31

CDC versus Change Tracking (2) 31

Slide 32

Demo Change Tracking Change Data Capture 32

Slide 33

Agenda About SQL Server 2008 Enhancements in SSMS and T-SQL Syntax T-SQL upgrades and information sorts Tracking Changing Data Partitioning Enhancements 33

Slide 34

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

Slide 35

Lock Escalation: The Problem Query 1 Query 2 Partitioned Table IX X ESCALATE overhaul redesign Partition 2 Partition 1 Partition 3 ` FG2 FG1 FG3 35 Lock acceleration on apportioned tables bolts ALL allotments Only approach to explain this at present is to incapacitate heightening

Slide 36

Lock Escalation: The Solution Query 1 Query 2 Partitioned Table IX ESCALATE redesign upgrade X Partition 2 Partition 1 Partition 3 FG1 FG2 FG3 36 SQL Server 2008 permits bolt heightening to the parcel level Escalation to segment level does not square inquiries on different segments

Slide 37

Demo Partitioning Enhancements: Partition-Level Lock Escalation 37

Slide 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

Slide 39

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

Recommended
View more...