Delighters in SQL Server 2008

Two of the new T-SQL enhancements in SQL Server 2008 are the inline variable initialization and compound assignment (also called delighters). We had those for a long time in languages like C, C++, C#, VB, and they are just coming to T-SQL. While not of significant value, it will make writing code easier and more compact.

Here are a couple examples to illustrate their use.

-- Declare and initialize variable

DECLARE @count INT = 1;


-- Compound assignments

SET @count += 1;

SET @count /= 2;

SET @count *= 5;

SET @count %= 3;

SET @count -= 1;


SELECT @count;




-- Result





-- Create sample table for employee pay rates


 employee_nbr INT PRIMARY KEY,

 pay_rate DECIMAL(15, 2),

 performance_score INT);


-- Insert data using the new row constructors

INSERT INTO PayRates VALUES (1, 40.00, 5), (2, 45.50, 4), (3, 39.50, 6);


-- Apply pay rate increase

-- Compound assignments can be used with columns on the right side


SET pay_rate *= performance_score * 0.25;


SELECT employee_nbr, pay_rate, performance_score

FROM PayRates;


-- Declare variable and initialize using query

DECLARE @max_pay_rate DECIMAL(15, 2) = (SELECT MAX(pay_rate)

                                        FROM PayRates);


SELECT @max_pay_rate AS max_pay_rate;

2 replies
  1. Kent Waldrop
    Kent Waldrop says:

    In addition to the compound assignment operators listed, the '+=' operator also works for concatenation and there are compound assignment operators for the bit operators:

    &= for 'and'
    |= for 'or'
    ^= for 'exclusive or'

    I don't know how useful these are but I tested them to make sure they function in SQL

    Kent Waldrop

  2. Anonymous
    Anonymous says:


    This doesn't seem to work in SSMS 2008. I get the following error:

    Msg 139, Level 15, State 1, Line 3
    Cannot assign a default value to a local variable.
    Msg 137, Level 15, State 2, Line 21
    Must declare the variable '@count'.


Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *