Transact-SQL Delighters (A few of my favorite things)

I stumbled upon a few little gems that SQL Server 2008 includes that I hadn’t yet heard about. Although, very few are deploying SQL 2008 to production currently, these enhancements might make life programming in 2008 much easier.

Compound Assignment Operators

How many times have you written the following statement?

SET @i = @i + 1

SQL Server 2008 implements additional assignment operators that most other languages already include. Now, you can simply write:

SET @i += 1

The statement adds and assigns the value in a single operation. There are several other available:

• += (plus equals)
• -=  (minus equals)
• *=  (multiplication equals)
• /=  (division equals)
• %=  (modulo equals)

Not a huge time saver, but I definitely appreciated the enhancement from a code readability apect.

Declare and Initialize Variables

Another tiny enhancement, but this one I’ll probably make more use of, especially when running test queries.

DECLARE @FirstName AS VARCHAR(25) = 'Taylor'

See that? We skipped the whole SET portion. You can also perform multiple declarations as you do now – simply separate by comma as usual. Pretty handy indeed.

Table Value Constructor Support through the VALUES Clause

Ever instantiated a table and needed to insert some data into it right away? Of course… and you probably did it like this:

INSERT INTO dbo.TableName
SELECT 1, 'One'
UNION ALL
SELECT 2, 'Two'
UNION ALL
SELECT 3, 'Three'

As you probably expected, SQL Server 2008 lets you do things a slightly new way:

INSERT INTO dbo.TableName
VALUES
(1, 'One'),
(2, 'Two'),
(3, 'Three')

As with the other items, this is certainly not a deal maker, but a "nice to have".