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
1
 
*/
 
-- Create sample table for employee pay rates
CREATE TABLE PayRates (
 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
UPDATE PayRates
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;

Labels: ,