SQL 2008 – TSQL Enhancements

SQL 2008 – TSQL Enhancements

• Justin King
• Architect SSW
• http://kingjustin.com/
• http://www.ssw.com.au/

Agenda

1. Platform changes
2. XML Enhancements
3. Compound Operators
4. Row Constructors
5. MERGE keyword
6. Table Valued Parameters
7. Sparse Columns
8. Date Time enhancements
9. Filestream
10. Spatial data
11. Hierarchy Data Type

Platform Changes (Justin’s top 8)

1. Database Mirroring enhancements
2. Policy Based Management
3. Auditing
4. Data Compression
5. BACKUP Compression
6. Powershell Integration
7. Transparent Database Encryption
8. Change tracking on databases

XML Enhancements

• Improved schema validation capabilities
• Enhancements to XQuery support
• Enhanced functionality for performing XML data manipulation language (DML) insertions
• Full timezone support for data/time types (including xs:date, xs:time)
• http://www.microsoft.com/sqlserve/2008/en/us/wp-sql-2008-whats-new-xml.aspx

Compound Operators

• Can now declare and initialize variables in the same statement
• +=
• -=
• /=
• *=
• %=

Row Constructors

• Values clause returns relational table with multiple rows
• Use with INSERT statement to insert multiple rows as an atomic operation;

Change Tracking

• Version of the data
• Most commonly used for entity data models

MERGE Keyword

• UPSERT
• Atomic statement combining INSERT, UPDATE and DELETE operations based on conditional logic
• Set-based operation; more efficient than multiple separate operations
• MERGE is defined by ANSI SQL; you will find it in other database platforms as well e.g. Oracle
• Useful in both OLTP and Data Warehouse environments
  • OLTP: merging recent info from external source
  • DW: incremental updates of fact, slowly changing dimensions

Table Valued Parameters

• CREATE TYPE [typeName] AS TABLE
• Great for passing in table parameters to a stored proc
• Strongly type variables
• Helps address the need to pass array elements to stored proc/functions

Sparse Columns

• Ordinary Columns optimised for NULL values
• No space used unless data added (4 byes extra if you do use)
• Behaves the same for end user

Date Time enhancements

• New date and time types!
• DATE – ANSI compliant
• TIME – ANSI compliant
• DATETIMEOFFSET – TZ aware DateTime
• DATETIME2 – DateTime with variable precision and larger range support

Filestream

• Stores binary files on file system
• Can’t use with MIRROR-ed databases
• Varbinary(max) – gets over 2GB limit
• Not accessible by files only by SQL Server
• Reduce size of database and backups

Spatial Data

• Two new system CLR data types
• GEOMETRY
• GEOGRAPHY

Hierarchy Data Type

• New system CLR type supporting trees
• Internally stored as Varbinary <= 900 bytes
• Holds a path that provides…

Resources

• Books Online
• TechEd DVD’s
• PDC Videos
• MSDN Webcasts
• Greg Low SQL Downunder podcast