Tuesday, February 19, 2013 4:22 AM
is table variables stored in database & is it visible as like temp tables(stored in tempDB)?
if yes, could you please let me know where it will be.
Thanks in advance
Tuesday, February 19, 2013 4:38 AM
select count(*) from tempdb..sysobjects go declare @t table ( a int ) select count(*) from tempdb..sysobjects go
- Marked As Answer by Muni329 Tuesday, February 19, 2013 7:07 PM
Tuesday, February 19, 2013 4:54 AM
Yes, table variables just like temp-tables are stored in tempdb database.
Tuesday, February 19, 2013 5:37 AM
Both temp table and table variables will be stored in the tempdb database.
There are questions about what is the max size of the table variable, max size of the temp table. according to online blogs and experts comments both table variable and temp table have a storage limit of tempdatabase size.
How ever when dealing with transactions temp tables have some advantages.
check the link below:
Tuesday, February 19, 2013 5:53 AM
Both temp table and table variable gets stored in tempdb database, you can check them using this..
select COUNT(*) from tempdb..sysobjects
Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh
Tuesday, February 19, 2013 6:52 AMModerator
> is it visible as like temp tables(stored in tempDB
Table variables are not visible like temporary tables.
Table variables scope is a batch, a stored procedure or a UDF.
Temporary tables scope is a connection(session), a stored procedure or until it's dropped.
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, February 19, 2013 7:04 AM