New t-SQL features for SQL 2k8


Inline Variable Assignments

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:

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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s