Variables in T-SQL behave differently from what people may be used to in languages like C or C#. The scope of a variable is the batch in which it is declared. This may not be intuitive to people who are used to local variables. So let’s look at an interesting example:

DECLARE @counter INT

SET @counter = 0

WHILE @counter < 100

    BEGIN

        DECLARE @value INT

        SET @value = ISNULL(@value, 0) + 5

        SET @counter = @counter + 1

    END

SELECT  @value

In this example we have a simple loop. The loop counter is a variable @counter. What is interesting that inside the loop we have a variable declaraion for @variable. We assign it a value (if it was null we set it to 5, if it was not null, we increment it by 5). Does this make sense? How could I ever consider the previous value if I’ve just declared the variable? What is unusual for many developers, is that the declaration is evaluated only once. This means that in the second iteration of the loop the @value variable is already declared and set, so we are incrementing it by five in every subsequent iteration.

At the end of the loop we can access the @value variable (do not forget that the scope is the whole batch), and the value will be 500 (100 iterations, 100 times adding 5).

Variable declarations are evaluated at the time of parsing, and the scope is the whole batch. Because of this, you cannot have two declare statements for the same variable in a batch, even if the only one declaration is reachable in the code. For example, the following statement will result in a parsing error:

IF 1 = 1

    DECLARE @a INT

ELSE

    DECLARE @a INT

The error is:

Msg 134, Level 15, State 1, Line 4

The variable name ‘@a’ has already been declared. Variable names must be unique within a query batch or stored procedure.

If you would like to know more about how transient data, like the above variable, is handled in SQL Server, come to my session at SQL Bits in Birmingham (UK) on 1 March, 2008. Like the previous SQL Bits event it is free. The last one was held in Reading half a year ago, and with 20 sessions and over 300 people attending it, it provides a unique opportunity to learn about SQL Server, as well as to meet your fellow DBAs and SQL developers in the UK. I will talk about transient data in SQL Server, and you can learn about what is stored in tempdb, how the tempdb can be a performance bottleneck, what the differences between temporary tables and table variables are, what is stored in the transaction log file and how its can size be kept under control.