New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. is against this unjust law - help us

A Space for All Things .NET Related

SQL 2008 – TSQL Enhancements

By James Hippolite, in , posted: 6-Dec-2008 16:15

SQL 2008 – TSQL Enhancements  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  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
  • 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
  • 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
  • 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
 Hierarchy Data Type
  • New system CLR type supporting trees
  • Internally stored as Varbinary <= 900 bytes
  • Holds a path that provides…
  • Books Online
  • TechEd DVD’s
  • PDC Videos
  • MSDN Webcasts
  • Greg Low SQL Downunder podcast

Other related posts:
GeekPost Weekly Newsletter Volume 4 Issue 7
MSDN Unplugged Roadshow Wellington
Right Sizing Agile Development - MSDN Magazine Webinar

Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
New Zealand

Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".