02 TSQL v4

UTS Short CourseSQL Server 2005for Developers: 

UTS Short Course SQL Server 2005 for Developers

Course Website: 

Course Website http://us.ssw.com.au/ssw/events/2007SQL/ Course Timetable Course Materials

About Justin: 

About Justin Senior Software Architect for www.ssw.com.au – C# Developer working in ASP.NET and Winforms. Java background using Swing with Hibernate Working with new technologies WPF, WCF and Visual Studio Team System 2008 Email: JustinKing@ssw.com.au Blog: http://kingjustin.com

Session 2: T-SQL Enhancements: 

Session 2: T-SQL Enhancements

What Will We Cover?: 

What Will We Cover? New Data Types DDL Triggers Ranking Functions CTE (Common Table Expressions) TOP %, XML Queries Exception Handling PIVOT/UNPIVOT Session 2: T-SQL Enhancements

New Data Types: 

New Data Types XML – Use Xquery to query, first class data type e.g Create Table candidates(id int, Resume XML); Select Id, Resume.query(‘\\Education’) FROM Candidates; varchar(max)/nvarchar(max) Large, recommended over TEXT going forward (2gig data in single column) and can index. Comparisons, triggers, concatenation, aggregates. Previously 8K limit for entire table (3 X varchar(3000) so would go TEXT columns) Varbinary(max) – replacement for image columns , up to 2GIG   Can create CLR types as well.

DDL Triggers: 

DDL Triggers

Ranking Functions: 

Ranking Functions

PIVOT: 

PIVOT

CTE (Common Table Expression) Before: 

CTE (Common Table Expression) Before

CTE (Common Table Expressions) After: 

CTE (Common Table Expressions) After

More features: 

More features

Session 2 Lab : 

Session 2 Lab T-SQL Enhancements Download from Course Materials Site (to copy/paste scripts) or type manually: http://us.ssw.com.au/ssw/events/2007SQL/

Where Else Can I Get Help?: 

Free chats and TechEd webcasts List of newsgroups Microsoft community sites Community events and columns Where Else Can I Get Help? www.microsoft.com/technet/community