Variable tables inside a while loop are not initialized every time: SQL Server

Question

I am wondering why table variables inside a while loop do not behave like other variables. Variable tables are created only once and will be used throughout the cycle. but other variables get initialized every time the loop grows.

Check out the code below for more information.

declare @tt int
set @tt =10
while @tt>0
begin

        declare @temptable table(id int identity(1,1),sid bigint)
        insert into @temptable 
                select @tt union all
                select @tt + 1 

                select * from @temptable 
               --delete from @temptable
                set @tt=@tt-1
end

- this is mistake?

Answer 1

Your premise is incorrect. Other variables do not receive reinitialization each time the declare statement is encountered.

set nocount on

declare @tt int
set @tt =10
while @tt>0
begin

        declare @i int

        set @i = isnull(@i,0) + 1
        print @i
        set @tt=@tt-1

end

Print

1
2
...
9
10

Answer 2

As expected

SQL Server - //, /

http://msdn.microsoft.com/en-us/library/ms187953.aspx:

- Transact-SQL, . , , .

Answer 3

, wann

set nocount on
declare @tt int
set @tt =10
while @tt>0
begin
        declare @i int=0
        set @i = @i + 1
        print @i
        set @tt=@tt-1
end

Results:
1
1
1
1
1
1
1
1
1
1

Answer 4

If you want to load a table variable every time a loop is executed. DROP FROM @Tablevariable after doing work inside a loop.