SQL Server : T-SQL features in SQL Server 2008 (Part 1)

Welcome to SQL Server 2008 T-SQL features buddy :)

Assign Default Values to Local Variables in SQL Server 2008

SQL Server 2008 allow us to assign a default value to a Local Variable when we DECLARE a variable itself instead of using separate SET statement :

DECLARE @iTest AS INT = 100, @vText AS VARCHAR(25) = 'SQL Server 2008';

SELECT @iTest AS Result1,@vText AS Result2;

The same logic can be done in SQL Server 2005 as below:

DECLARE @iTest AS INT, @vText AS VARCHAR(25);

SET @iTest = 1;

SET @vText = 'SQL Server 2005';

SELECT @iTest AS Result1,@vText AS Result2;

Compound assignment operators in SQL Server 2008

SQL Server 2008 introduces new 5 compound assignment operators :-

(a) += (plus equals)

(b) -= (minus equals)

(c) *= (multiplication equals)

(d) /= (division equals)

(e) %= (modulo equals)

These operators can be used in the SET clause of an UPDATE statement Or in a SET statement that assigns values to variables as shown below :

DECLARE @iTest AS INT = 10;

SET @iTest += 15;

SELECT @iTest AS Result1;

The result would be 25.

The same logic can be done in SQL Server 2005 as below:

DECLARE @iTest AS INT;

SET @iTest = 10;

SET @iTest = @iTest + 15;

SELECT @iTest AS Result1;

You could see in SQL Server 2008 directly we assigned 10 as default value to a Variable (@iTest)and added 15 to @iTest directly, whereas in SQL Server 2005 first declared a Variable and assigned 10 as initial value using SET statement and again added 15 to @iTest variable.

Using the above features, we can reduce some unnecessary code to improve the performance.

Keep follow my blog for other new SQL Server 2008 T-SQL features....... :)