SQL server variable : loop vs duplicate?

sql-server, sql-server-2008

Solution

The placing of the `DECLARE` is immaterial (except the parser won't allow you to try and use it before the `DECLARE`)

It only actually declares one variable irrespective of how many times the block of code containing the declaration is executed.

The `DECLARE` is not in itself an executable statement. e.g.

IF 1 = 0
BEGIN
DECLARE @I INT
END

SELECT @I

Works fine even though that block is never entered. The memory for the variables is reserved at compilation time before execution of the query even begins in the execution context.

One way of seeing this is

DBCC FREEPROCCACHE;

GO

SELECT  m2.pages_allocated_count
        --If 2012 use the next line instead
        --,m2.pages_in_bytes/m2.page_size_in_bytes as pages_allocated_count
        ,m2.page_size_in_bytes
FROM   sys.dm_exec_cached_plans cp
       CROSS apply sys.dm_exec_sql_text(cp.plan_handle) t
       JOIN sys.dm_os_memory_objects m1 ON m1.memory_object_address = cp.memory_object_address
       JOIN sys.dm_os_memory_objects m2 ON m1.page_allocator_address = m2.page_allocator_address
WHERE  text LIKE '%this query%'
 AND m2.type = 'MEMOBJ_EXECUTE'

DECLARE @A0 VARCHAR(8000);
DECLARE @A1 VARCHAR(8000);
DECLARE @A2 VARCHAR(8000);
DECLARE @A3 VARCHAR(8000);
DECLARE @A4 VARCHAR(8000);
DECLARE @A5 VARCHAR(8000);
DECLARE @A6 VARCHAR(8000);
DECLARE @A7 VARCHAR(8000);
DECLARE @A8 VARCHAR(8000);
DECLARE @A9 VARCHAR(8000);
DECLARE @A10 VARCHAR(8000);
DECLARE @A11 VARCHAR(8000);
DECLARE @A12 VARCHAR(8000);
DECLARE @A13 VARCHAR(8000);
DECLARE @A14 VARCHAR(8000);
DECLARE @A15 VARCHAR(8000);
DECLARE @A16 VARCHAR(8000);
DECLARE @A17 VARCHAR(8000);
DECLARE @A18 VARCHAR(8000);
DECLARE @A19 VARCHAR(8000);
DECLARE @A20 VARCHAR(8000);

which shows memory reserved for the current query, if you adjust the number of variables declared you will see the memory reserved change even though the `DECLARE` block is right at the end of the batch.

Problem

Why can I declare the same variable name in a loop but not allowed without a loop ? ``` DECLARE @loop INT SET @loop = 0 WHILE @loop<5 BEGIN DECLARE @t INT -- <-- This is called multiple times SET @t = 1 SET @loop = @loop+1 SELECT @loop END ``` - This will execute : 1,2,3,4,5 while this : ``` DECLARE @t INT SET @t = 1 DECLARE @t INT SET @t = 1 ``` will obviously say : - The variable name '@t' has already been declared.

Original source