T-SQL Upgrades in SQL Server 2008

I've been working on a series of videos touting some of the new features and benefits of SQL Server 2008. I've spent a lot of time working with SQL Server 2008 and reading through Books Online (BOL) to catch the biggest changes. But there are a bunch of smaller changes and improvements that frequently get overlooked, so I thought I’d cover a few of them that have really made my life easier over the past few months.

Inline Variable Assignments

As a database developer, I spend a lot of time writing stored procedures and playing around with variables. Since I also spend plenty of time programming in C# 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:

DECLARE @now datetime = GETDATE()

SELECT @now [now]

This minor change helps improve code readability by letting you instantiate variables and assign their initial value inline while simultaneously providing a minor productivity boost as well.

Row Constructors

MySQL has always had two features that I really wished SQL Server would 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. Sadly though, I doubt that SQL Server will ever get a feature like this as the folks at Microsoft probably think that Bulk Copy is perfect for this kind of operation. (Though the number of developers or DBAs that I know who actually have any Bulk Copy chops is very small as the technology isn't very approachable.)

Rants about SQL Server's lack of a dump feature aside, the second feature that I always wished that SQL Server would 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:

CREATE TABLE UserTypes (

      UserTypeId tinyint IDENTITY(1,1) NOT NULL,

      UserType varchar(20) NOT NULL

)

INSERT INTO UserTypes (UserType)

VALUES ('User')

INSERT INTO UserTypes (UserType)

VALUES ('Manager')

INSERT INTO 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:

INSERT INTO UserTypes (UserType)

SELECT 'User'

UNION SELECT 'Manager'

UNION SELECT '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:

CREATE TABLE UserTypes (

      UserTypeId tinyint IDENTITY(1,1) NOT NULL,

      UserType varchar(20) NOT NULL

)

INSERT INTO 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 I've mentioned, these aren't life-savers by any stretch of the imagination, but they do make some interactions patently 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 tersely like so:

-- 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.