In-line variable Initialization and Compound Assignment

SQL Server 2008 supports in-line variable initialization and compound assignment, a feature which developers have been enjoying for years. This feature saves lot of code and it makes it more readable.

In line Variable Assignment

--SQL Server 2005

DECLARE @i INT

SET @i = 1

SELECT @i AS VALUE

GO

--SQL Server 2008

DECLARE @i INT = 2

SELECT @i AS VALUE

GO

Compound Assignment:

--Simple variable

DECLARE @i INT = 3

SELECT @i += 1

SELECT @i

GO

--DML Statements

--Create table

CREATE TABLE Color

(ID int, ColorName varchar(10))

GO

--Populate Staging table

INSERT INTO Color VALUES (1, 'Red') , (2, 'Blue') , (3, 'Green')

GO

SELECT ColorName FROM Color WHERE ID = 1 --returns Red

UPDATE Color SET ColorName += '-B'

WHERE ID = 1

SELECT ColorName FROM Color WHERE ID = 1 --returns Red-B