locked
Table Varaible RRS feed

  • Question

  • I had 10000000 rows in a table variable. Actually these records where it will be stored in sql server 2005?

    Please explain me

    Wednesday, July 20, 2011 2:39 AM

Answers

  • These records are stored in tempDB, but it's not a good idea to place such huge volume of records into table variable. Once your batch will finish, the records are not going to be stored (or at least you will not be able to access them anymore).
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by KJian_ Tuesday, July 26, 2011 8:32 AM
    Wednesday, July 20, 2011 3:14 AM
  • A table variable will have a structure created in tempdb.  Just like a temp table will.  For purposes of what is stored in memory versus what is stored in tempdb, table variables are treated the same as temp tables.  If there is memory available, the recently used data will be kept in memory.  But if there is not enough memory available, some or all of the data will be written to disk.  If you want to check that a table is really created in tempdb when you declare a table variable, you can run

    Select 'This query won''t find anything'
    
    select o.name as TableName, c.name as ColumnName, o.type_desc as ObjectType 
    from tempdb.sys.columns c
    inner join tempdb.sys.objects o on c.object_id = o.object_id
    where c.name in ('fooTest1', 'FooTest2')
    go
    
    declare @ttt TABLE(fooTest1 int, FooTest2 int)
    
    Select 'Since a table variable has been declared, now that table and columns exist'
    select o.name as TableName, c.name as ColumnName, o.type_desc as ObjectType 
    from tempdb.sys.columns c
    inner join tempdb.sys.objects o on c.object_id = o.object_id
    where c.name in ('fooTest1', 'FooTest2')
    go
    
    Select 'Since the go ended the previous batch, the table is now gone'
    
    select o.name as TableName, c.name as ColumnName, o.type_desc as ObjectType 
    from tempdb.sys.columns c
    inner join tempdb.sys.objects o on c.object_id = o.object_id
    where c.name in ('fooTest1', 'FooTest2')
    go
    
    


    You will see that when the table variable is in scope, there is a table for that table variable in tempdb.

    Tom

    • Proposed as answer by Surendra Nath GM Wednesday, July 20, 2011 1:22 PM
    • Marked as answer by KJian_ Tuesday, July 26, 2011 8:32 AM
    Wednesday, July 20, 2011 3:29 AM

All replies

  • These records are stored in tempDB, but it's not a good idea to place such huge volume of records into table variable. Once your batch will finish, the records are not going to be stored (or at least you will not be able to access them anymore).
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by KJian_ Tuesday, July 26, 2011 8:32 AM
    Wednesday, July 20, 2011 3:14 AM
  • A table variable will have a structure created in tempdb.  Just like a temp table will.  For purposes of what is stored in memory versus what is stored in tempdb, table variables are treated the same as temp tables.  If there is memory available, the recently used data will be kept in memory.  But if there is not enough memory available, some or all of the data will be written to disk.  If you want to check that a table is really created in tempdb when you declare a table variable, you can run

    Select 'This query won''t find anything'
    
    select o.name as TableName, c.name as ColumnName, o.type_desc as ObjectType 
    from tempdb.sys.columns c
    inner join tempdb.sys.objects o on c.object_id = o.object_id
    where c.name in ('fooTest1', 'FooTest2')
    go
    
    declare @ttt TABLE(fooTest1 int, FooTest2 int)
    
    Select 'Since a table variable has been declared, now that table and columns exist'
    select o.name as TableName, c.name as ColumnName, o.type_desc as ObjectType 
    from tempdb.sys.columns c
    inner join tempdb.sys.objects o on c.object_id = o.object_id
    where c.name in ('fooTest1', 'FooTest2')
    go
    
    Select 'Since the go ended the previous batch, the table is now gone'
    
    select o.name as TableName, c.name as ColumnName, o.type_desc as ObjectType 
    from tempdb.sys.columns c
    inner join tempdb.sys.objects o on c.object_id = o.object_id
    where c.name in ('fooTest1', 'FooTest2')
    go
    
    


    You will see that when the table variable is in scope, there is a table for that table variable in tempdb.

    Tom

    • Proposed as answer by Surendra Nath GM Wednesday, July 20, 2011 1:22 PM
    • Marked as answer by KJian_ Tuesday, July 26, 2011 8:32 AM
    Wednesday, July 20, 2011 3:29 AM