table variable vs temp table

Question

declare  @i int = 1
if (@i= 2)
Begin 
    declare @t table (value int)
    insert into @t 
    select 1
    select * from @t
end
else 
    select *from @t  
---------------
declare  @i int = 1
if (@i= 2)
Begin 
    create table #t(value int)
    insert into #t
    select 1
end
else 
    select *from #t

Why table variable is not getting invalid object name in this?

This qüestion is answered here https://stackoverflow.com/questions/5994957/sql-server-variable-scope-in-a-stored-procedure — Doliveras, Aug 10 '17 at 10:14
Possible duplicate of [SQL Server variable scope in a stored procedure](https://stackoverflow.com/questions/5994957/sql-server-variable-scope-in-a-stored-procedure) — Doliveras, Aug 10 '17 at 10:15
For the same reason as this returns a row. `SELECT * FROM tempdb.sys.columns WHERE name = 'foobar';DECLARE @T TABLE(foobar int);` — Martin Smith, Aug 10 '17 at 10:19
Possible duplicate of [What's the difference between a temp table and table variable in SQL Server?](https://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server) — Tanner, Aug 10 '17 at 10:19
i dupe voted based on title and retracted it, please be more specific in your title — Tanner, Aug 10 '17 at 10:21
I seem to remember reading something about sql server executing the declare statement at compile time, not at run time. However it's a very vague memory and I wouldn't trust that without more research. — Zohar Peled, Aug 10 '17 at 10:23
The if statement is false still how SQL server is declaring the table variable. — omkar, Aug 10 '17 at 18:28
@ZoharPeled yep, space is allocated for scalar variables up front in the execution context and table variables are also created and cached linked to the execution context. — Martin Smith, Aug 10 '17 at 18:42
@omkar `declare` isn't an executable statement. It doesn't have to be executed. The parser won't let you reference the variable until later than the declaration but it doesn't matter if at runtime that bit of code is entered. The execution plan is compiled including the variable before execution even begins. — Martin Smith, Aug 10 '17 at 18:44

Noor A Shuvo · Answer 1 · 2017-08-10T11:32:48.747

2

The scope of a variable in T-SQL is not confined to a block. The scope of a local variable is the batch in which it is declared.

There was a request to make it possible to declare variables that are only visible within a block but Microsoft denied it. Here is the link

edited Aug 10 '17 at 11:32

answered Aug 10 '17 at 10:35

Noor A Shuvo

2,639
3
23
48

score 1 · Answer 2 · answered Aug 10 '17 at 10:32

1

The documentation specifies:

The scope of a local variable is the batch in which it is declared.

To be honest, I thought the scope was the block where the definition occurs. I suppose the real definition is that the scope is from the point of definition to the end of the batch.

A batch is usually defined by GO or by connections.

answered Aug 10 '17 at 10:32

Gordon Linoff

1,242,037
58
646
786

Yes, the batch is correct - but this does not explain, why it is declared even if the "IF" is not fullfilled.... – Tyron78 Aug 10 '17 at 10:50
yes in the first place it should not have entered the if block to execute declare @t table – omkar Aug 10 '17 at 10:56

score 0 · Answer 3 · answered Aug 10 '17 at 10:25

I guess SQL Server is smart enough to extraxt the "declare @t table (value int)" and execute it first and then perform all other actions. This can be proven by putting an "insert into @t values(6)" in the "ELSE" part of your statement. Your second example - the one with the DDL for the temp table - is indeed executed in the designed order, so the object is not created in your example.

table variable vs temp table

3 Answers3