New t-SQL features for SQL 2k8

When wearing my developer hat, I spend a lot of time writing stored procedures and playing around with variables. Since I also spend plenty of time programming I’ve always been a bit jealous of the ability to new-up a variable in C# (or VB.NET) to a specified value. For example, prior to SQL Server 2008, if I wanted to create a new datetime variable and set it equal to right now, I’d have to do that in two steps, like so:

DECLARE @now datetime

SET @now =GETDATE()SELECT @now [now]

But with SQL Server 2008, I can do that in a single line of code, as follows:

MySQL has two features that many db pros want SQL Server to adopt. The first is the dump function, which allows you to dump an entire table or database to a DDL script along with supporting DML INSERT scripts that make it easy to persist an entire table or database to a single text file. This file can then be executed on another server to recreate any output tables and repopulate them with data by executing the accompanying INSERT statements.

The second feature that is frequently referenced that SQL Server could borrow from MySQL is what some have called Bulk Inserts or the ability to INSERT multiple, static, rows with a single INSERT statement. For example, if I’m creating a simple table defining user types and I want to populate it with some initial values, each value will require its own INSERT statement like so:

CREATETABLE UserTypes(

UserTypeId tinyintIDENTITY(1,1)NOTNULL,

UserType varchar(20)NOTNULL

)

INSERTINTO UserTypes(UserType)

VALUES (‘User’)

INSERTINTO UserTypes(UserType)

VALUES (‘Manager’)

INSERTINTO UserTypes(UserType)

VALUES (‘Admin’)

I’ve always been a bit lazy when populating tables in this fashion and have gotten into the habit of just SELECTing rows into my table using a UNION, mostly because I hate typing VALUES and open/close parenthesis:

INSERTINTO UserTypes(UserType)

SELECT‘User’

UNIONSELECT‘Manager’

UNIONSELECT‘Admin’

But SQL Server 2008 has caught up with MySQL, and I can now insert multiple rows within a single INSERT statement with T-SQL 2008’s new Row Constructors:

CREATETABLE UserTypes(

UserTypeId tinyintIDENTITY(1,1)NOTNULL,

UserType varchar(20)NOTNULL

)

INSERTINTO UserTypes(UserType)

VALUES (‘User’),(‘Manager’),(‘Admin’)

Again, this isn’t a huge or earth-shattering change, but a very nice improvement that can really help boost usability and increase productivity. (Too bad there’s no sensible way to ditch those parenthesis though.)

Compound Assignment Operators

Another great, but subtle, improvement to T-SQL in SQL Server 2008 is the inclusion of new Compound Assignment Operators (though Microsoft just calls them Compound Operators). Like the other improvements mentioned, these aren’t life-savers by any stretch of the imagination, but they do make some interactions easier, especially if you’re comfortable with the greater range of assignment operators available in C# (and VB.NET ).

For example, prior to SQL Server 2008, if you wanted to append text or increment a variable, you needed to do it in a rather verbose manner, as follows:

— declare:

DECLARE @int int

DECLARE @string varchar(10)

— initialize:

SET @int = 5

SET @string =‘123’

— increment:

SET @int = @int + 5

SET @string = @string +‘456’

SELECT @int, @string

But with SQL Server 2008, you can do it a bit more logically and more concise:

— declare and init:

DECLARE @int int= 5

DECLARE @string varchar(10)=‘123’

— increment:

SET @int += 5

SET @string +=‘456’

SELECT @int, @string

These new assignment operators work with all of the standard mathematical assignment operators including bitwise operations which can help make interacting with bitmaps a bit easier as well.

Making Your Life Easier

Ultimately, all three of these new additions are rather minor when compared to things like Full Text Integration, sparse columns, FILESTREAM storage, Table-Valued Parameters, and a host of other improvements and additions. But they all represent subtle improvements that can really help to both improve productivity and ease usability, so they’re definitely worth adding to your repertoire.