SQL Server 2008 for Developers

http://johnsterrett.com/

John Sterrett, @JohnSterrett

About Me!

• Reside in Wheeling, WV (Pittsburgh, PA) • Syndicated Blogger on SQLServerPedia • Writer for MSSQLTIPS.com

• Working with SQL Server since 2006 • Production DBA for 2 Years • Responsible for 50 Production instances with 1250 Databases • .NET Developer for 3 Years • Built a few Data Marts Session Code • Session Title

Topics to Cover…..

• • Management Studio (SSMS) Enhancements • T-SQL Delighters • Filtered Indexes • New Date and Time Data Types • Table-Valued Parameters •

MERGE statement

• Policy Based Management

Change Data Capture

Session Code • Session Title

Management Studio Enhancements

• IntelliSense • Debugging • Error List • Object Explorer Details Viewer • Template Explorer • Activity Monitor Session Code • Session Title

Transact-SQL Delighters

• Declare and Initialize Variables • Compound assignment Operators • Table Value Constructor Support through the VALUES Clause Session Code • Session Title

DEMO!!!

Demo covering The following topics • IntelliSense • Debugging • Error Lists • Object Explorer Details • Template Explorer • Activity Monitor Session Code • Session Title

Filtered Indexes!

Have you ever wanted to create an index that filters between a static search criteria?

CREATE INDEX idxName ON Schema.Table

(Column) WHERE …… Session Code • Session Title

Filtered Indexes!

• Improved query performance and plan quality if used correctly!

• Reduced index maintenance costs • Reduced index storage costs Session Code • Session Title

DEMO

Filtered Indexes Demo….

Session Code • Session Title

New Date & Time Types

• SQL Server 2008 extends date/time support • Larger Value Space – Current DATETIME - 1753-9999 Years – Current DATETIME - 0.00333 Second Accuracy – New Date Types - 0001-9999 Years – New Date/Time Types - Precisions to 100 nanoseconds • Variable Precision Saves Space • Separate Date and Time Saves Space • ANSI Compatible Session Code • Session Title

Date and Time

DATE Data Type • Date Only • 01-01-0001 to 31-12-9999 Gregorian Calendar TIME Data Type • Time Only • Variable Precision - 0 to 7 decimal places for seconds • To 100 nanoseconds Session Code • Session Title

DATETIME2 & DATETIMEOFFSET

DATETIME2 Data Type

• 01-01-0001 to 31-12-9999 Gregorian Calendar • Variable Precision - to 100 nanoseconds

DATETIMEOFFSET

• 01-01-0001 to 31-12-9999 Gregorian Calendar • Variable Precision - to 100 nanoseconds • Time Zone Offset (From UTCTime) Preserved • Not Time Zone Aware - No Daylight Saving Time Support Session Code • Session Title

DATETIMEOFFSET

• SWITCHOFFSET (DATETIMEOFFSET, time_zone) • Allows you to change time zones • SqlDbType.DateTimeOffset

Session Code • Session Title

Demo!!

T-SQL Date and Time DATETIMEOFFSET with .NET

Session Code • Session Title

Table-Value Parameters

Have you wished that you could send a table into a stored procedure?

Do you wish you could send all parent-child relationship data in one round-trip?

Session Code • Session Title

Table Types

SQL Server has table variables • DECLARE @t TABLE (id int); SQL Server 2008 adds strongly typed table variables • CREATE TYPE mytab AS TABLE (id int); DECLARE @t mytab; Table-Value Parameters must use strongly typed table variables Session Code • Session Title

Things to know about TVP

ADO.NET 3.5 Supports Table Value Parameters You can populate TVP from the following .NET objects • DataTable • DbDataReader • System.Collections.Generic.IList Recommended for 1000 or less records Session Code • Session Title

TVP with .NET

SqlParameter tvpParam = new SqlParameter(); tvpParam.ParameterName = "@TVP"; tvpParam.Value = dtList; //DataTable in this example tvpParam.SqlDbType = SqlDbType.Structured; // This data type allows us to pass a table into DB Objects tvpParam.TypeName = "TVPComponentType"; // Name of strong typed (table type) Session Code • Session Title

Demo!!

Table-Value Parameters with T-SQL Table-Value Parameters with ASP.NET (C#) Session Code • Session Title

MERGE!!!

TRUNCATE TABLE db1.dbo.fool

INSERT INTO db1.dbo.fool

SELECT * FROM db2.dbo.fool

Session Code • Session Title

Merge Statement

Multiple set operations in a single SQL statement Uses multiple sets as input • MERGE target USING source ON ...

Operations can be INSERT, UPDATE, DELETE Operations based on • WHEN MATCHED • WHEN [TARGET] NOT MATCHED • WHEN SOURCE NOT MATCHED ANSI SQL 2006 compliant - with extensions Session Code • Session Title

More on MERGE

Multiple WHEN clauses possible • For MATCHED and SOURCE NOT MATCHED • Only one WHEN clause for TARGET NOT MATCHED MERGE can be used with any table source A MERGE statement causes triggers to be fired once Rows affected includes total rows affected by all clauses Session Code • Session Title

MERGE Performance

MERGE statement is transactional • No explicit transaction required One Pass Through Tables • At most a full outer join • Matching rows = when matched • Left-outer join rows = when target not matched • Right-outer join rows = when source not matched Session Code • Session Title

DEMO!!

Demo Merge with T-SQL!

Session Code • Session Title

Central Management System (CMS) & Policy Based Management

Have you ever wanted to run a script against all production databases?

Have you ever wanted to enforce naming conventions in SQL Programming?

Session Code • Session Title

CMS and PBM

• Central Management System • • • Needs only 1 instance of SQL 2008 Reactive not proactive great tool for updating objects on multiple servers • Policy Based Management • • proactive tool prevents stuff from happening great tool to enforce compliance on SQL 2008+ servers Session Code • Session Title

Policy Based Management

• Facets • Conditions • Policies Session Code • Session Title

DEMO!

Demo monitoring and enforcing naming standards with Policy Based Management Session Code • Session Title

Change Data Capture

Have you ever wanted to get row deltas on a table without using triggers on the source table?

Session Code • Session Title

Enable Change Data Capture

• Must have Enterprise Edition • Uses transaction log to pull deltas • Enable on database (req sysadmin) • • • creates cdc schema creates cdc user • creates system tables with cdc schema • Enable on table(s) (req db_owner) • sys.sp_cdc_enable_db_change_data_capture

sys.sp_cdc_enable_table

• • creates sql agent jobs creates table cdc.schema_tablename_CT

Session Code • Session Title

Change Data Capture data

• Changes stored in cdc.schema_tablename_CT

• _$operation column specifies change type • • • • 1 = DELETE 2 = INSERT 3 = BEFORE UPDATE 4 = AFTER UPDATE Session Code • Session Title

Querying Change data

• cdc.fn_cdc_get_all_changes • gets all changes during specific range • cdc.fn_cdc_get_net_changes • returns final (net change) for each row in the specific range Session Code • Session Title

Session Code • Session Title

SSIS with CDC

DEMO!

Enable and use Change Data Capture Session Code • Session Title

How do you get started?

Using the Transact-SQL Debugger SQL 2008 JumpStart MSDN – SQL 2008 Virtual Labs Developer Fundamentals Part 1: Uncovering T-SQL on SQL Server 2008 Session Code • Session Title

Thank you

for attending this session and the 2010 PASS Summit in Seattle