Table variable is always useful for less data. If the result set returns a large number of records, we need to go for temp table. Posted by Haseeb Kayani
LinkedIn Group: PASS Professional Association for SQL Server
When generating the execution plan, SQL can evaluate the statistics on temp tables, but unaware of it on a temp variable. this is the biggest difference that will put the queries at a very poor performance.
A major difference is that statistics are not maintained for table variables, but are for temp tables. If you pwill sotre much data in either, you may get a better execution plan with a temp table than you would with a table variable.
1. Transaction logs are not recorded for table var and therefore cannot participate in begin tran... commit or rollback
2. Scope : table var is visible only in current block when temp table is visible in current connection (i.e : current and inner procedures), global temp table is visible for all connections
Please note that BOTH table variables and temp tables are stored in tempdb
Below are few more 1. Table variables cannot be used in a “SELECT select_list INTO table_variable” statement. 2. Although a table variable is a variable, it cannot be assigned to another table variable. 3. Transactions against table variables last only for the duration of the update, therefore they require less locking and logging resources. 4. You cannot truncate a table variable. 5. You cannot insert explicit values into an identity column (the table variable does not support the SET IDENTITY_INSERT ON). 6. For Temp tables columns cannot be defined with user-defined data types (UDDT) that are not created in tempdb; you must use the native data types. (UDDTs are specific to the database, and temporary tables belong to tempdb.) Since the tempdb database is recreated each time SQL Server starts, you could use a startup stored procedure to add the UDDT to tempdb. Alternatively, since the model database is the baseline that all databases are created from, you could add the UDDT to the model database and it will be incorporated into tempdb when it is created. 7. you cannot perform any DDL statements against a table variable. For instance, you might have a need to populate a table, and then add an index or column. In this case, you will need to use a temporary table. 8. Collation: table variables use the collation of the current database. Temporary tables use the collation of the tempdb database. If they are not compatible, then you will need to specify the collation to use in either the queries or the table definition. 9. If you want to use a table variable in dynamic SQL, you must define the table variable in the dynamic SQL code. A temporary table that was created before calling the dynamic SQL can be used inside the dynamic SQL.
And some addition to what Bill has mentioned.
SQL cannot build statistics against a table variable, like it can against temporary tables. This means that the execution plan for table variables will always treat the table as if it has 1 row. Obviously, this means that it might not use the best execution plan. Note that the estimated execution plan for both table variables and temporary tables will show 1 row; however the actual execution plan for temporary tables will change once SQL has recompiled the stored procedure. Also note that if a temporary table doesn’t exist, you will get an “Invalid object name ‘’” error when creating the estimated execution plan. Posted by Rohit Paliwal
I would say that a table variable doesn't exist on a physical storage device, but then I recall asking this question myself a few years ago and getting answers back which suggested that in some cases where there is a lot of data, it may actually exist.
I can say that from a performance standpoint, table variables are often bad news. I have found that using a permanent table, truncating it each time and then using it as if it were temporary is many times faster than trying to manipulate that data in a table variable.
I do think though that it really comes down a lot to the usage and amounts of data that are being talked about.
Check the following blog post for all differences between them and their usage: http://sqlwithmanoj.wordpress.com/2010/05/15/temporary-tables-vs-table-variables/
Marcelo the statement that "temp table must resides on disc, besides a variable is always on memory" is a myth. It all depends on the size of the tables. Search on the SQLServerCentral and you will find a lot of blog posts about it.
Both table-variables & temporary-tables are created, exist and grow in tempdb. It is a misconsecption among many people that table-variables are managed in memory.
Check this link: http://sqlwithmanoj.wordpress.com/2010/07/20/table-variables-are-not-stored-in-memory-but-in-tempdb/ Posted by Manoj Pandey
Yes, I agree: "...Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb..." http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
@Mohamed, I believe that while the db engine may use the tempdb to build the table variable, the completed accessable table variable is held completly in memory. If you do a select on the table variable, the column values are pulled from memory and not the tempdb.
@All - Here is what the BOL (2008) list as advantages/considerations:
table variables provide the following benefits:
A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.
Within its scope, a table variable can be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table cannot be used in the following statement:
SELECT select_list INTO table_variable
table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.
CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.
table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used.
Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.
Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics. For more information about temporary tables, see CREATE TABLE (Transact-SQL).
table variables can be referenced by name in the FROM clause of a batch, as shown the following example:
SELECT Employee_ID, Department_ID FROM @MyTableVar
Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:
SELECT EmployeeID, DepartmentID FROM @MyTableVar m JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND m.DepartmentID = Employee.DepartmentID)
Assignment operation between table variables is not supported. Also, because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.
The definition of the table variable exists in SQL server memory. If the SQL server memory manager determines that the data set can be supported by the available memory (keep in mind that the SQL server will allocate memory to other higher priority components whenever it deems necessary) the data set will materialize in memory, otherwise the data set will materialize in tempdb.
The relative performance of temp tables vs table variables is dictated by the size of the data set (and the predicate expression operating over the data set). If the data set is small enough to be materialized in available memory than the table variable will out perform a temp table (for the simple fact that the latency inherent in writing data to disk is avoided).
So performance in this context is governed by the availability of SQL server memory, competition for memory from higher priority components, and the size of the data set. Lastly, if the predicate expression operating against the data set contained in the table variable forces the use of tempdb (e.g., order by, group by, etc.) then the performance will degrade due to the writes into tempdb.
Due to MSFT's lack of adequate public documentation around this topic tthe behaivor of queries that use table variables are commonly mis-understood. As usual, if there are 10 facts the consumer needs to understand then MSFT will only document 1 of the 10 (the 1 that makes the problem appear to be much simpler than it inherenly is).
LinkedIn Group: SQLServerCentral Thank you Charles for a better answer than mine. As this materialisation of the table variable would be into tempdb, then also bear in mind that it is advised to have one file per core assigned to the SQL Server for tempdb, rather than the default of a single file. I had to choose my words carefully there to avoid confusion as processors tend to be multi-core these days... Posted by Simon Clark
Another difference is that SQL Server assumes that a table variable only contains 1 row which can lead the optimizer to make a poor choice when creating an execution plan if the table variable contains significantly more than 1 row. (An exact figure for 'significantly' is hard to establish, but > 100 rows is often mentioned). You can find that out and lots more besides by following the links that others have posted.
I though the myth about temp tables being on disk and table variables in memory had been thoroughly debunked by now, but it seems that not everyone is aware of that. Posted by Liam North
one of the most important considerations for using temp tables or table variables is that table variables do not support parallelism Posted by Hermann Cardenas
I don't believe SQL Server "assumes" that a table variable contains only 1 row. A table variable isn't the best choice if you wish to store lots and lots of rows because a table variable cannot have NC indexes (beyond those that support PRIMARY KEY and UNIQUE constraints. A good think about table variables (because of their very limited scope) is NO LOCKING! And no crazy stored procedure recompiles. Good stuff.
@Liam Yes. NO LOCKS is an overstatement. How about drastically fewer locks because of the scope of a table variable (private to the process that creates it). Better? Simple Talk's Phil Factor explains this well. http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/ One Row?? Seriously? :-) Posted by Mike Bishop
LinkedIn Group: SQL Server Professionals Discussion:What's the difference between a temp table and table variable in SQL Server?
@Mike Bishop
Perhaps "assumes" isn't the right word, but the point I was trying to make is that because no statistics are kept on a table variable, having table variables that contain a large number of rows is generally not a good idea. With regard to locking, I don't think it's true to say that tables variable will cause NO LOCKS, but fewer locks than other structures (temporary or otherwise) - source: http://msdn.microsoft.com/en-us/library/ms175010.aspx Agree about the indexes. Posted by Liam North
I was asked this same question during a job interview question long ago, and after NOT satisfying the interviewer (even after waving the white flag), it seems like EVERYONE has a different answer, even now, years later!!!
So here is my TAKE.
One, Table Variables reside in memory, RAM, "almost" all the time. I think this cause I wrote this really intense T-SQL batch file that created lots and lots of temp tables and rows and it took a really long time, like an hour or so. Then I took the same T-SQL batch file and swapped out the temp tables with table variables and this same batch file took like 1 to 3 minutes! The improvement was like 20, 30, 40 times faster!!!
So, I THINK table variables will use RAM, memory, FIRST....before going to disk. SQL Server will only use disk for table variables ONLY if your table variables are really really big. I mean REALLY, REALLY BIG.
You can theoretically say,
"HEY, I am going to create this really really big table variable and I am going to keep filling this table variable with rows until it fills up all of RAM and crashes the server. However, SQL Server says, 'NO YOU are NOT going to CRASH me, since I don't have any usable RAM left and I will start using disk, just like a temp table. So don't try crashing me or I will fill up your hard drive when you are not looking.'"
So, as far as I am concerned, EVEN if SQL Server did exclusively use only disk for even table variables, I wouldn't want SQL Server to use disk in the first place, because the purpose, for at least me, wanting to use table variables in the first place is SPEED, SPEED, SPEED. And that means RAM for table variables....almost all the time.
So, that's my take, as well as how table variables should act, even if SQL Server doesn't work that way, but from my own tests, it sure seems like table variables do use memory before disk. Posted by Philip Chin
No! This is a complete fallacy. Both temp tables and table variables can reside in memory or spill to disk, depending on their size, i.e. how much data they contain. At least read the various comments before posting. No wonder you didn't satisfy the interviewer!
@Philip, Liam is right. You might have gone through all comments but I think didn't go thru the comments section on the blog link you provided.
Check my comments above where I've posted link to my blog post which proves that both gets creates and resides in tempdb their entire life. Link: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
Also MSDN blogs are not always 100% correct, they just represent people's opinions which might vary. Do check the second last comment in provided by Martin Smith. Posted by Manoj Pandey
I did read your post carefully. The part that I was referring to when I posted my comment was when you say "...since I don't have any usable RAM left and I will start using disk, just like a temp table". I took that to mean that you believe that temp tables will ALWAYS use disk, which is incorrect. If that's not what you meant, i.e you meant that table variables will spill to disk like temp tables [will spill to disk], then I apologise.
As for saying exactly how and when disk rather than memory is used for temp tables and table variables, it is impossible to say because it depends upon the amount of RAM available to SQL Server and the number of rows (or more correctly the number of pages) the table variable or temp table contains.
I agree with you that tables variables will reside in memory as long as there is sufficient memory to accommodate them; the same is true for temp tables.
@Manoj Prandey
Thanks for backing me up, but I'm afraid I have to disagree with that blog post link. It is entirely possible for objects in any database including tempdb to reside in memory. Indeed, the more of a database that can be stored in memory, the faster queries that run against the database will be. Posted by Liam North
LinkedIn Group: SQL Server Professionals @Manoj In that blog post, However, this NEW functionality in SQL Server 2008 for passing an entire table via input output parameters is exactly WHY I DO NOT pass tables in and out of SQL Server using table variables and it's because it does use DISK and you are right when doing this particular passing of entire tables for SQL Server 2008. But I don't remember these table variables being able to pass entire tables in and out of SQL Server existing in SQL Server 2003 and 2000.But let's really get to the WHY's because that counts more than "What's the difference between a table variable and temp table?" Instead of talking about LOCKS, catalogs and all that other stuff, let get to the WHY's regarding a table variable and why it should be used to begin with. It's to use MEMORY over DISK. But even more than memory over disk, it's ultimately performance....speed, get stuff done!!!! And I do NOT use table variables to pass in tables from say c# code via in out parameters for disk, nor would I recommend that users do so if they need want a lot of performance because it uses disk. In fact, I am particularly DISAPPOINTED with SQL Server 2008 that table variables use DISK when passing in a table via input output parameters. I said to myself, "that's not right and that's counter to why table variables were invented in the first place. I want my table variables to only reside in memory and I don't want it touching the disk whatsoever. If something goes wrong in my stored procedure, I already used transactions and the less changes i wrote to disk, the better, if there is a rollback." Furthermore, it should be very possible to determine when a table variable will use memory. Most SQL Server production and development servers have adequate RAM and if it doesn't, one shouldn't even be using that server to begin with. Next, who in the world is using a table variable to manipulate say more than a Gigabyte of data AND doing so on a server that has, say only 2 Gigabytes of RAM? If they are, I cannot believe it will be a transactional or production environment like say a real time shopping cart or auction system. But say if it's an OLAP or some analysis or reports. If speed is super important, then again, it's table variables because they SHOULD be using memory over disk. But say if the dataset is really really big, well then management will eventually know it's big and they will allocate enough dollars to pay for MORE RAM so they can run table variables over temp tables and do so confidently knowing it better not be hitting disk otherwise tests and running of reports can take a long time and maybe have to run overnight and HENCE paying SQL Server DEVELOPERS for more TIME spent waiting around for batches to end. That's not PROFESSIONAL to do that. Side note, there is a reason why everyone is moving to SSD's as well as all these companies creating bigger and cheaper SSD's. There is this guy on this YouTube that said, forget NoSql, de-normalization and all these other NoSql variants and just spend the bucks for an SSD. In a year or two, that's going to happen but I can't seem to find that YouTube video of that guy. Lastly, isn't it called a table "variable" because they are like other "variables" in SQL Server, C, VB.NET, C, C++, etc. and because these "variables" should only reside in memory as well as act like these other variables in all the other languages in regards to memory over disk? If not, then shouldn't these table variables be called something like a table "file" or pseudo table files as opposed to a "variable" then? Posted by Philip Chin
Hi @Philip, nobody's hurt, but can you prove what you've mentioned above.
I can prove my point and thus have provided the link. Can you challenge it, if yes then I'll learn something new.
Here's another proof that table variable are in tempdb and not in memory, check this: http://www.scarydba.com/2009/10/13/table-variables-are-only-in-memory-fact-or-myth/ Posted by Manoj Pandey
This is my last post of this thread too and it's a good indicator of why there is still such a degree of misinformation about this subject; just look at the debate we've had here and we're still not (fully) in agreement. Quoting from that ScaryDBA link you posted: '..both will reside completely in memory or will swap out to the disk through tempdb, depending on their size.' ...which is exactly what I said. If that's what Grant Fritchey (the ScaryDBA) says then it's good enough for me! Posted by Liam North
@Manoj - OK, I read that LINK at http://msdn.microsoft.com/en-us/library/ms345368(SQL.100).aspx
I think it's wrong for at least two reasons.
For one, that snippet of text you mention above, if you do the following two (2) deletions. (A), if you take out the three words, "and/or table variable" and (B) also take out those two sentence at the top of the snippet as shown below:
"A table variable behaves like a local variable. A table variable is of type table and is primarily used for the temporary storage of a set of rows that are returned as the result set of a table-valued function."
At least for me, I can guess that the snippet was originally written only for temporary tables. And then, at a later date, I am guessing, someone just added the words, "and table variable" or "or table variable" to wherever they saw the words "temporary table".
So it seems to me like the words "and table variable" and "or table variable" were shoe-horned (e.g. added, force-fit, hammered in) to that text at some later date.
Furthermore, this text somewhat contradicts itself. First it says, "A table variable behaves like a local variable." and then later on it says, "Local temporary tables and variables are cached..." What? CACHED?
So at first it is saying it "behaves" like a local variable. For me, "behaving" like a local variable would at least mean to me, created/stored in memory just like all the other local variables as I don't know of any local variables stored to disk on creation or when assigned a value.
Second, it says table variables are cached? Doesn't caching mean storing in memory? If so, that means to me that table variables are stored in memory for a 2nd time? A 2nd time??
So if "behaving like local variable" means created/stored, then it doesn't make sense to store in them memory a 2nd time, at least not for me it doesn't.
Otherwise, I think someone at Microsoft should clarify what "behaving like a local variable" means in regards to table variables.
TWO, I think checking the hard drive light is a good test. It worked for me long ago and I have never had any problems, performance or otherwise, using table variables since. Posted by Philip Chin
I said, "*ALMOST* all the time" in memory. The key word, is "*ALMOST" and obviously you didn't read my post whatsoever.
And yes I did read the various comments before I posted and guess what? There is still disagreement and confusion and PLUS, see that link:
SQL Server Storage Engine Blog - TempDB:: Table variable vs local temporary table http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
The MSDN Blog should be technically correct, HOWEVER, using BOTH memory AND disk is not clearly emphasized NOR does it clearly reflect what goes in a REAL LIVE PRODUCTION ENVIRONMENT.
That fact you keep saying BOTH memory and disk is essentially what the MSDN blog said. But you didn't say, "WHEN" and "HOW OFTEN" in real productions environments the memory versus disk is used for table variables. And as I said before, table variables SHOULD be using memory *ALMOST* ALL THE TIME, otherwise why even invent and design into the SQL Server engine the table variable to begin with when there is already the temp table.
By the way, that interviewer from long ago was, in my opinion, was one of those "know-it-all's" and told me he/she didn't actually write "code any more" and just managed people. He also sounded like one of those Certified MSDN types who spend more time getting certifications and taking tests as opposed to writing production code. ;-) Posted by Philip Chin
Let's get a copy of SQL Server 2000, 2003 and 2008.
And do these two (2) tests for each version of SQL Server And preferably not on a production server and not with VMware or any virtualization either.
-- DO THESE TESTS AT YOUR OWN RISK
-- ********** -- TEST #1 --Create lots and lots of temp tables and fill them with say 10,000,000 and wrap this in say, while loop
WHILE (x < 1000000) -- create 1,000,000 tables, or more or less and to see when disk space is "obviously" being used BEGIN -- Create temp table #someTempTable -- fill 10,000,000 rows of temp table END
-- WATCH DISK SPACE, WATCH MEMORY -- BUT ALSO WATCH HARD DRIVE LIGHT WITH VIDEO CAMERA -- ----------------------------------------------------------------------------------
-- LET ABOVE TRANSACTION or BATCH FILE END
-- REBOOT MACHINE if you want a clean slate.
-- ********** -- TEST #2 --START a new batch file AFTER the TEMP Table batch file above has totally ended. -- DO ABOVE except with table variables WHILE (x < 1000000) -- create 1,000,000 table variables, or maybe less variables BEGIN -- Create table variable DECLARE @TBLVariable (FirstColumnStuff varchar(128)) -- fill 10,000,000 rows of table variables you just created END
-- ACTUALLY WATCH HARD DRIVE LIGHT and ACTUALLY WATCH MEMORY
-- BUT please don't just look at counters as the hard drive light is very important
-- TIME BOTH TESTS and let's see the difference.
-- IF I see that hard drive flashing like crazy for a temp table and the table variable hardly flashes, then that tells me something. -- IF I see the total test time change dramatically between the two tests that also tells me something.
As far as I am concerned, I did use temp tables years ago, but when table variables came around (I think in SQL Server 2000), I have only used them, table variables, since, cause I only needed something "temporary" to begin with...just like any other variable.
I would not be surprised if the SQL Server team made some adjustments to the temp tables to use memory before disk in later versions of SQL Server and even service packs, otherwise, why invent the table variable to begin with? Posted by Philip Chin
The very single reason why table variables are persisted on disk is because of ACIDity. Every single table, be it user, temporary or variable, is persisted on disk in order to fulfill ACIDity.
Now SQL Server is exclusively working in RAM, because it's way faster than accessing to disks. As simple as that. And that's why it takes all the RAM it can if it's not controlled by the max server memory option, but that's another debate.
Now SQL Server, as it's been said before, does not maintain statistics on table variables, and that leads SQL Server to make incorrect cardinality estimations, unless we have a PRIMARY KEY constraint on it, or we add an OPTION (RECOMPILE) query hint at the query that involves such a table.
The reason why Philip is seeing a tremendous performance improvement in switching from table variables to temporary tables is because statistics on the latter exist and are maintained very frequently (6 rows modifications at least and we get a statistics automatic update).
That said, generally programming with table variables and temporary tables is usually a "workaround" to bad queries written because of an incorrect data model.
Whenever we can, we must use CTEs as a better alternative, because then cardinality estimation is possible, and this does not cause contention to allocation pages in TempDB.
Bear in mind that TempDB is a system database, and must be used as such. It is the sandbox of SQL Server for many processes, such as sorts, hashing, row versionning, ...
One last thing : TempDB is optimized since SQL Server 2005 to have a table cache of 32 tables which DDL is different, so that when a table variable is out of use, it does not deallocate all the pages, but leaves one Index Allocation Map page and one data page allocated. That avoids creating "hot points" in allocation pages, because then SQL Server does not need to access all the allocation pages when the workload relies a lot on table variables.
@Philip Chin : read SQL Server 2008 Internals by Kalen Delaney. There's a lot in there, and it's a Microsoft Press book. There's also The Guru's Guide to SQL Server Architecture and Internals, by Ken Henderson.
@Nicolos Souquet: The definition of ACID is (atomicity, consistency, isolation, durability).
Should not Table Variables act like other '@' like variables in SQL Server?
From a design standpoint of SQL Server, if table variables are persisted on disk, should not other variables that start with a '@' also be persisted on disk?
Should not the fact that it's called a table *variable* mean that it is temporary which is the opposite of the 'D" in ACID, durability. If something bad happens to the server, should not the table variable should be cleared out just like all the other @ variables on REBOOT? Posted by Philip Chin
@Manoj Once I saw the performance difference between table variables and temp tables, I never looked back.
RULE ONE, at least for me.. Always use table variables for temporary stuff, just like @variables are used for temporary stuff in a stored procedure.
Other RULES for me: If you need to create table variables, it should be a real need, typically something to simplify, a complex query. Aggregates, sums, or maybe to make a CTE easier to use or give the results in the format I need. A "temporary / intermediate " table *variable* to simplify a future query that can be done on the table variable. And it should almost never need more than a single simple index. By the way, I like to follow the KISS - Keep It Simple Stupid philosophy.
Otherwise, if it needs a lot of indexes or even all the statistics, then it must be a complex table and most likely multiple queries are needed to run against it. If so, then why not create a regular table and forget both the temp table and the table variable and then truncate the regular table at the end of the batch or stored procedure. This way you can more easily use Enterprise Manager to debug and inspect and other stuff on what you are doing as the problem was complex, correct?
DO THOSE BOOKS ANSWER THE ORIGINAL QUESTION? You see those books you mentioned (one published in 2003). If they clearly explained the why's and differences between the temp table and table variables, WHY, after more than 9 years since the first book published in 2003, did the ORIGINAL PERSON POST this QUESTION in the FIRST PLACE to this discussion?
Moreover, if those books are so effective, why did MSDN have to post another blog (see that link mentioned previously dated March 2008) on this same question almost 8 years after table variable were first introduced? In fact, there are so many blogs and discussions on this "table variable vs temp table" it is truly amazing.
See Google "temp table vs table variable" - 220,000 results for this same question? Wow! http://tinyurl.com/7lddtxr Posted by Philip Chin
@Manoj - "Rather than giving a practical example you are giving a psudocode... and Hard disk lights... c'mon what a ridiculous example... lol :)"
Try it and just fill in the blanks for pseudo code as basically I am creating lots of table variable and temp tables and filing them up via the WHILE loop
I also forgot to add the line to increment the counter SET x = x +1 so it doesn't endlessly loop.
I don't know why looking at hard drive lights is not a valid test method? Like I said before, I follow the KISS - Keep It Simple Stupid philosophy.
Nevertheless, I guess you can use an IO meter test if you wish, but looking at the hard drive light is more simple. Posted by Philip Chin
LinkedIn Group: SQL Server Professionals
ReplyDeleteAFAIK, a temp table must resides on disc, besides a variable is always on memory.
Posted by Marcelo Lucas Guimarães
LinkedIn Group: SQL Server Professionals
ReplyDeleteDiscussion:What's the difference between a temp table and table variable in SQL Server?
Links:
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
http://www.sql-server-performance.com/2007/temp-tables-vs-variables/
http://www.sqlteam.com/article/using-table-variables
Posted by Marcelo Lucas Guimarães
LinkedIn Group: SQL Server DBA
ReplyDeleteTable variable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.
Posted by Haseeb Kayani
LinkedIn Group: PASS Professional Association for SQL Server
ReplyDeleteWhen generating the execution plan, SQL can evaluate the statistics on temp tables, but unaware of it on a temp variable. this is the biggest difference that will put the queries at a very poor performance.
Posted by Peng Lv
LinkedIn Group: SQL Server DBA
ReplyDeleteA major difference is that statistics are not maintained for table variables, but are for temp tables. If you pwill sotre much data in either, you may get a better execution plan with a temp table than you would with a table variable.
Posted by Bill Sheets
LinkedIn Group: SQL Server DBA
ReplyDeleteTwo major diff :
1. Transaction logs are not recorded for table var and therefore cannot participate in begin tran... commit or rollback
2. Scope : table var is visible only in current block when temp table is visible in current connection (i.e : current and inner procedures), global temp table is visible for all connections
Please note that BOTH table variables and temp tables are stored in tempdb
Posted by Mohamed Bouarroudj
LinkedIn Group: SQL Server DBA
ReplyDeleteBelow are few more
1. Table variables cannot be used in a “SELECT select_list INTO table_variable” statement.
2. Although a table variable is a variable, it cannot be assigned to another table variable.
3. Transactions against table variables last only for the duration of the update, therefore they require less locking and logging resources.
4. You cannot truncate a table variable.
5. You cannot insert explicit values into an identity column (the table variable does not support the SET IDENTITY_INSERT ON).
6. For Temp tables columns cannot be defined with user-defined data types (UDDT) that are not created in tempdb; you must use the native data types. (UDDTs are specific to the database, and temporary tables belong to tempdb.) Since the tempdb database is recreated each time SQL Server starts, you could use a startup stored procedure to add the UDDT to tempdb. Alternatively, since the model database is the baseline that all databases are created from, you could add the UDDT to the model database and it will be incorporated into tempdb when it is created.
7. you cannot perform any DDL statements against a table variable. For instance, you might have a need to populate a table, and then add an index or column. In this case, you will need to use a temporary table.
8. Collation: table variables use the collation of the current database. Temporary tables use the collation of the tempdb database. If they are not compatible, then you will need to specify the collation to use in either the queries or the table definition.
9. If you want to use a table variable in dynamic SQL, you must define the table variable in the dynamic SQL code. A temporary table that was created before calling the dynamic SQL can be used inside the dynamic SQL.
And some addition to what Bill has mentioned.
SQL cannot build statistics against a table variable, like it can against temporary tables. This means that the execution plan for table variables will always treat the table as if it has 1 row. Obviously, this means that it might not use the best execution plan. Note that the estimated execution plan for both table variables and temporary tables will show 1 row; however the actual execution plan for temporary tables will change once SQL has recompiled the stored procedure. Also note that if a temporary table doesn’t exist, you will get an “Invalid object name ‘’” error when creating the estimated execution plan.
Posted by Rohit Paliwal
LinkedIn SQLServerCentral
ReplyDeleteI would say that a table variable doesn't exist on a physical storage device, but then I recall asking this question myself a few years ago and getting answers back which suggested that in some cases where there is a lot of data, it may actually exist.
I can say that from a performance standpoint, table variables are often bad news. I have found that using a permanent table, truncating it each time and then using it as if it were temporary is many times faster than trying to manipulate that data in a table variable.
I do think though that it really comes down a lot to the usage and amounts of data that are being talked about.
Posted by Simon Clark
LinkedIn Group: SQL Server Elite
ReplyDeletetemp table:
stored in tempdb, can be rollback but when used in a procedure it can not be pre-compiled. Useful for large data handling
table variable:
stored in memory, cannot be rollback. when used in procedure, proc can be pre-compiled. Useful for small data handling
Posted by Oke Akoro
LinkedIn Group: SQL Server 2008
ReplyDeleteThe biggest difference is their impact on execution plans
Posted by Dan Decasse
LinkedIn Group: SQL Server Professionals
ReplyDeleteCheck the following blog post for all differences between them and their usage: http://sqlwithmanoj.wordpress.com/2010/05/15/temporary-tables-vs-table-variables/
Posted by Manoj Pandey
LinkedIn Group: SQL Server Professionals
ReplyDeleteMarcelo the statement that "temp table must resides on disc, besides a variable is always on memory" is a myth. It all depends on the size of the tables. Search on the SQLServerCentral and you will find a lot of blog posts about it.
Posted by Vladimir Sotirov
LinkedIn Group: SQL Server Professionals
ReplyDeleteHi @Marcelo, @Vladimir is correct.
Both table-variables & temporary-tables are created, exist and grow in tempdb. It is a misconsecption among many people that table-variables are managed in memory.
Check this link: http://sqlwithmanoj.wordpress.com/2010/07/20/table-variables-are-not-stored-in-memory-but-in-tempdb/
Posted by Manoj Pandey
LinkedIn Group: SQL Server Professionals
ReplyDeleteYes, I agree: "...Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb..." http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
Posted by Marcelo Lucas Guimarães
LinkedIn SQL Server DBA
ReplyDelete@Mohamed, I believe that while the db engine may use the tempdb to build the table variable, the completed accessable table variable is held completly in memory. If you do a select on the table variable, the column values are pulled from memory and not the tempdb.
@All - Here is what the BOL (2008) list as advantages/considerations:
table variables provide the following benefits:
A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in.
Within its scope, a table variable can be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table cannot be used in the following statement:
SELECT select_list INTO table_variable
table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.
CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.
table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used.
Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.
Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics. For more information about temporary tables, see CREATE TABLE (Transact-SQL).
table variables can be referenced by name in the FROM clause of a batch, as shown the following example:
SELECT Employee_ID, Department_ID FROM @MyTableVar
Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:
SELECT EmployeeID, DepartmentID
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
m.DepartmentID = Employee.DepartmentID)
Assignment operation between table variables is not supported. Also, because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.
Posted by Andrew Smith
LinkedIn PASS Professional Association for SQL Server
ReplyDeleteAlso statements with temporary tables in are recompiled each time they run where ones with table variable are not.
Posted by Peter Marriott
LinkedIn Group: SQLServerCentral
ReplyDeleteThe definition of the table variable exists in SQL server memory. If the SQL server memory manager determines that the data set can be supported by the available memory (keep in mind that the SQL server will allocate memory to other higher priority components whenever it deems necessary) the data set will materialize in memory, otherwise the data set will materialize in tempdb.
The relative performance of temp tables vs table variables is dictated by the size of the data set (and the predicate expression operating over the data set). If the data set is small enough to be materialized in available memory than the table variable will out perform a temp table (for the simple fact that the latency inherent in writing data to disk is avoided).
So performance in this context is governed by the availability of SQL server memory, competition for memory from higher priority components, and the size of the data set. Lastly, if the predicate expression operating against the data set contained in the table variable forces the use of tempdb (e.g., order by, group by, etc.) then the performance will degrade due to the writes into tempdb.
Due to MSFT's lack of adequate public documentation around this topic tthe behaivor of queries that use table variables are commonly mis-understood. As usual, if there are 10 facts the consumer needs to understand then MSFT will only document 1 of the 10 (the 1 that makes the problem appear to be much simpler than it inherenly is).
Posted by Charles Clifford
LinkedIn Group: SQLServerCentral
ReplyDeleteThank you Charles for a better answer than mine. As this materialisation of the table variable would be into tempdb, then also bear in mind that it is advised to have one file per core assigned to the SQL Server for tempdb, rather than the default of a single file. I had to choose my words carefully there to avoid confusion as processors tend to be multi-core these days...
Posted by Simon Clark
LinkedIn Group: PASS Professional Association for SQL Server
ReplyDeleteCTE may be resued without the need to recompile its definition. It provides good edge when called multiple times.
Posted by Atif Shehzad
Group: SQL Server Professionals
ReplyDeleteAnother difference is that SQL Server assumes that a table variable only contains 1 row which can lead the optimizer to make a poor choice when creating an execution plan if the table variable contains significantly more than 1 row. (An exact figure for 'significantly' is hard to establish, but > 100 rows is often mentioned).
You can find that out and lots more besides by following the links that others have posted.
I though the myth about temp tables being on disk and table variables in memory had been thoroughly debunked by now, but it seems that not everyone is aware of that.
Posted by Liam North
LinkedIn Group: SQL Server Professionals
ReplyDeleteOne of the most interesting is that temp table rollback if transaction rolled back. Table variable not. And they both have representation on disk!!!
Posted by Yochanan Rachamim
LinkedIn Group: SQL Server Professionals
ReplyDeleteone of the most important considerations for using temp tables or table variables is that table variables do not support parallelism
Posted by Hermann Cardenas
LinkedIn Group: SQL Server Professionals
ReplyDeleteI don't believe SQL Server "assumes" that a table variable contains only 1 row. A table variable isn't the best choice if you wish to store lots and lots of rows because a table variable cannot have NC indexes (beyond those that support PRIMARY KEY and UNIQUE constraints. A good think about table variables (because of their very limited scope) is NO LOCKING! And no crazy stored procedure recompiles. Good stuff.
Posted by Mike Bishop
LinkedIn Group: SQL Server Professionals
ReplyDelete@Liam
Yes. NO LOCKS is an overstatement. How about drastically fewer locks because of the scope of a table variable (private to the process that creates it). Better? Simple Talk's Phil Factor explains this well. http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/
One Row?? Seriously? :-)
Posted by Mike Bishop
LinkedIn Group: SQL Server Professionals
ReplyDeleteDiscussion:What's the difference between a temp table and table variable in SQL Server?
@Mike Bishop
Perhaps "assumes" isn't the right word, but the point I was trying to make is that because no statistics are kept on a table variable, having table variables that contain a large number of rows is generally not a good idea. With regard to locking, I don't think it's true to say that tables variable will cause NO LOCKS, but fewer locks than other structures (temporary or otherwise) - source: http://msdn.microsoft.com/en-us/library/ms175010.aspx
Agree about the indexes.
Posted by Liam North
LinkedIn Group: SQL Server Professionals
ReplyDeleteI was asked this same question during a job interview question long ago, and after NOT satisfying the interviewer (even after waving the white flag), it seems like EVERYONE has a different answer, even now, years later!!!
So here is my TAKE.
One, Table Variables reside in memory, RAM, "almost" all the time. I think this cause I wrote this really intense T-SQL batch file that created lots and lots of temp tables and rows and it took a really long time, like an hour or so. Then I took the same T-SQL batch file and swapped out the temp tables with table variables and this same batch file took like 1 to 3 minutes! The improvement was like 20, 30, 40 times faster!!!
So, I THINK table variables will use RAM, memory, FIRST....before going to disk. SQL Server will only use disk for table variables ONLY if your table variables are really really big. I mean REALLY, REALLY BIG.
You can theoretically say,
"HEY, I am going to create this really really big table variable and I am going to keep filling this table variable with rows until it fills up all of RAM and crashes the server. However, SQL Server says, 'NO YOU are NOT going to CRASH me, since I don't have any usable RAM left and I will start using disk, just like a temp table. So don't try crashing me or I will fill up your hard drive when you are not looking.'"
So, as far as I am concerned, EVEN if SQL Server did exclusively use only disk for even table variables, I wouldn't want SQL Server to use disk in the first place, because the purpose, for at least me, wanting to use table variables in the first place is SPEED, SPEED, SPEED. And that means RAM for table variables....almost all the time.
So, that's my take, as well as how table variables should act, even if SQL Server doesn't work that way, but from my own tests, it sure seems like table variables do use memory before disk.
Posted by Philip Chin
LinkedIn Group: SQL Server Professionals
ReplyDeleteNo! This is a complete fallacy. Both temp tables and table variables can reside in memory or spill to disk, depending on their size, i.e. how much data they contain. At least read the various comments before posting. No wonder you didn't satisfy the interviewer!
Posted by Liam North
LinkedIn Group: SQL Server Professionals
ReplyDelete@Philip, Liam is right. You might have gone through all comments but I think didn't go thru the comments section on the blog link you provided.
Check my comments above where I've posted link to my blog post which proves that both gets creates and resides in tempdb their entire life. Link: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
Also MSDN blogs are not always 100% correct, they just represent people's opinions which might vary. Do check the second last comment in provided by Martin Smith.
Posted by Manoj Pandey
LinkedIn Group: SQL Server Professionals
ReplyDelete@Phillip Chin
I did read your post carefully. The part that I was referring to when I posted my comment was when you say "...since I don't have any usable RAM left and I will start using disk, just like a temp table". I took that to mean that you believe that temp tables will ALWAYS use disk, which is incorrect. If that's not what you meant, i.e you meant that table variables will spill to disk like temp tables [will spill to disk], then I apologise.
As for saying exactly how and when disk rather than memory is used for temp tables and table variables, it is impossible to say because it depends upon the amount of RAM available to SQL Server and the number of rows (or more correctly the number of pages) the table variable or temp table contains.
I agree with you that tables variables will reside in memory as long as there is sufficient memory to accommodate them; the same is true for temp tables.
@Manoj Prandey
Thanks for backing me up, but I'm afraid I have to disagree with that blog post link. It is entirely possible for objects in any database including tempdb to reside in memory. Indeed, the more of a database that can be stored in memory, the faster queries that run against the database will be.
Posted by Liam North
LinkedIn Group: SQL Server Professionals
ReplyDelete@Manoj In that blog post,
However, this NEW functionality in SQL Server 2008 for passing an entire table via input output parameters is exactly WHY I DO NOT pass tables in and out of SQL Server using table variables and it's because it does use DISK and you are right when doing this particular passing of entire tables for SQL Server 2008. But I don't remember these table variables being able to pass entire tables in and out of SQL Server existing in SQL Server 2003 and 2000.But let's really get to the WHY's because that counts more than "What's the difference between a table variable and temp table?" Instead of talking about LOCKS, catalogs and all that other stuff, let get to the WHY's regarding a table variable and why it should be used to begin with. It's to use MEMORY over DISK. But even more than memory over disk, it's ultimately performance....speed, get stuff done!!!!
And I do NOT use table variables to pass in tables from say c# code via in out parameters for disk, nor would I recommend that users do so if they need want a lot of performance because it uses disk. In fact, I am particularly DISAPPOINTED with SQL Server 2008 that table variables use DISK when passing in a table via input output parameters. I said to myself, "that's not right and that's counter to why table variables were invented in the first place. I want my table variables to only reside in memory and I don't want it touching the disk whatsoever. If something goes wrong in my stored procedure, I already used transactions and the less changes i wrote to disk, the better, if there is a rollback."
Furthermore, it should be very possible to determine when a table variable will use memory. Most SQL Server production and development servers have adequate RAM and if it doesn't, one shouldn't even be using that server to begin with.
Next, who in the world is using a table variable to manipulate say more than a Gigabyte of data AND doing so on a server that has, say only 2 Gigabytes of RAM?
If they are, I cannot believe it will be a transactional or production environment like say a real time shopping cart or auction system. But say if it's an OLAP or some analysis or reports. If speed is super important, then again, it's table variables because they SHOULD be using memory over disk. But say if the dataset is really really big, well then management will eventually know it's big and they will allocate enough dollars to pay for MORE RAM so they can run table variables over temp tables and do so confidently knowing it better not be hitting disk otherwise tests and running of reports can take a long time and maybe have to run overnight and HENCE paying SQL Server DEVELOPERS for more TIME spent waiting around for batches to end. That's not PROFESSIONAL to do that.
Side note, there is a reason why everyone is moving to SSD's as well as all these companies creating bigger and cheaper SSD's. There is this guy on this YouTube that said, forget NoSql, de-normalization and all these other NoSql variants and just spend the bucks for an SSD. In a year or two, that's going to happen but I can't seem to find that YouTube video of that guy.
Lastly, isn't it called a table "variable" because they are like other "variables" in SQL Server, C, VB.NET, C, C++, etc. and because these "variables" should only reside in memory as well as act like these other variables in all the other languages in regards to memory over disk?
If not, then shouldn't these table variables be called something like a table "file" or pseudo table files as opposed to a "variable" then?
Posted by Philip Chin
LinkedIn Group: SQL Server Professionals
ReplyDeleteHi @Philip, nobody's hurt, but can you prove what you've mentioned above.
I can prove my point and thus have provided the link. Can you challenge it, if yes then I'll learn something new.
Here's another proof that table variable are in tempdb and not in memory, check this: http://www.scarydba.com/2009/10/13/table-variables-are-only-in-memory-fact-or-myth/
Posted by Manoj Pandey
LinkedIn Group: SQL Server Professionals
ReplyDelete@Manoj
This is my last post of this thread too and it's a good indicator of why there is still such a degree of misinformation about this subject; just look at the debate we've had here and we're still not (fully) in agreement.
Quoting from that ScaryDBA link you posted: '..both will reside completely in memory or will swap out to the disk through tempdb, depending on their size.' ...which is exactly what I said. If that's what Grant Fritchey (the ScaryDBA) says then it's good enough for me!
Posted by Liam North
LinkedIn Group: SQL Server Professionals
ReplyDelete@Manoj -
OK, I read that LINK at http://msdn.microsoft.com/en-us/library/ms345368(SQL.100).aspx
I think it's wrong for at least two reasons.
For one, that snippet of text you mention above, if you do the following two (2) deletions. (A), if you take out the three words, "and/or table variable" and (B) also take out those two sentence at the top of the snippet as shown below:
"A table variable behaves like a local variable. A table variable is of type table and
is primarily used for the temporary storage of a set of rows that are returned as
the result set of a table-valued function."
At least for me, I can guess that the snippet was originally written only for temporary tables. And then, at a later date, I am guessing, someone just added the words, "and table variable" or "or table variable" to wherever they saw the words "temporary table".
So it seems to me like the words "and table variable" and "or table variable" were shoe-horned (e.g. added, force-fit, hammered in) to that text at some later date.
Furthermore, this text somewhat contradicts itself. First it says, "A table variable behaves like a local variable." and then later on it says, "Local temporary tables and variables are cached..." What? CACHED?
So at first it is saying it "behaves" like a local variable. For me, "behaving" like a local variable would at least mean to me, created/stored in memory just like all the other local variables as I don't know of any local variables stored to disk on creation or when assigned a value.
Second, it says table variables are cached? Doesn't caching mean storing in memory? If so, that means to me that table variables are stored in memory for a 2nd time? A 2nd time??
So if "behaving like local variable" means created/stored, then it doesn't make sense to store in them memory a 2nd time, at least not for me it doesn't.
Otherwise, I think someone at Microsoft should clarify what "behaving like a local variable" means in regards to table variables.
TWO, I think checking the hard drive light is a good test.
It worked for me long ago and I have never had any problems, performance or otherwise, using table variables since.
Posted by Philip Chin
LinkedIn Group: SQL Server Professionals
ReplyDeleteHow about reading my post carefully.
I said, "*ALMOST* all the time" in memory. The key word, is "*ALMOST" and obviously you didn't read my post whatsoever.
And yes I did read the various comments before I posted and guess what? There is still disagreement and confusion and PLUS, see that link:
SQL Server Storage Engine Blog - TempDB:: Table variable vs local temporary table
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
The MSDN Blog should be technically correct, HOWEVER, using BOTH memory AND disk is not clearly emphasized NOR does it clearly reflect what goes in a REAL LIVE PRODUCTION ENVIRONMENT.
That fact you keep saying BOTH memory and disk is essentially what the MSDN blog said. But you didn't say, "WHEN" and "HOW OFTEN" in real productions environments the memory versus disk is used for table variables. And as I said before, table variables SHOULD be using memory *ALMOST* ALL THE TIME, otherwise why even invent and design into the SQL Server engine the table variable to begin with when there is already the temp table.
By the way, that interviewer from long ago was, in my opinion, was one of those "know-it-all's" and told me he/she didn't actually write "code any more" and just managed people. He also sounded like one of those Certified MSDN types who spend more time getting certifications and taking tests as opposed to writing production code. ;-)
Posted by Philip Chin
LinkedIn Group: SQL Server Professionals
ReplyDeleteLet's get a copy of SQL Server 2000, 2003 and 2008.
And do these two (2) tests for each version of SQL Server
And preferably not on a production server and not with VMware or any virtualization either.
-- DO THESE TESTS AT YOUR OWN RISK
-- **********
-- TEST #1
--Create lots and lots of temp tables and fill them with say 10,000,000 and wrap this in say, while loop
WHILE (x < 1000000) -- create 1,000,000 tables, or more or less and to see when disk space is "obviously" being used
BEGIN
-- Create temp table #someTempTable
-- fill 10,000,000 rows of temp table
END
-- WATCH DISK SPACE, WATCH MEMORY
-- BUT ALSO WATCH HARD DRIVE LIGHT WITH VIDEO CAMERA
-- ----------------------------------------------------------------------------------
-- LET ABOVE TRANSACTION or BATCH FILE END
-- REBOOT MACHINE if you want a clean slate.
-- **********
-- TEST #2
--START a new batch file AFTER the TEMP Table batch file above has totally ended.
-- DO ABOVE except with table variables
WHILE (x < 1000000) -- create 1,000,000 table variables, or maybe less variables
BEGIN
-- Create table variable DECLARE @TBLVariable (FirstColumnStuff varchar(128))
-- fill 10,000,000 rows of table variables you just created
END
-- ACTUALLY WATCH HARD DRIVE LIGHT and ACTUALLY WATCH MEMORY
-- BUT please don't just look at counters as the hard drive light is very important
-- TIME BOTH TESTS and let's see the difference.
-- IF I see that hard drive flashing like crazy for a temp table and the table variable hardly flashes, then that tells me something.
-- IF I see the total test time change dramatically between the two tests that also tells me something.
As far as I am concerned, I did use temp tables years ago, but when table variables came around (I think in SQL Server 2000), I have only used them, table variables, since, cause I only needed something "temporary" to begin with...just like any other variable.
I would not be surprised if the SQL Server team made some adjustments to the temp tables to use memory before disk in later versions of SQL Server and even service packs, otherwise, why invent the table variable to begin with?
Posted by Philip Chin
LinkedIn Group: SQL Server Professionals
ReplyDeleteSide note, I amazed, after more than TEN (10) years this very same debate on temp tables and table variables still rages on.
Posted by Philip Chin
LinkedIn Group: SQL Server Professionals
ReplyDeleteThe very single reason why table variables are persisted on disk is because of ACIDity. Every single table, be it user, temporary or variable, is persisted on disk in order to fulfill ACIDity.
Now SQL Server is exclusively working in RAM, because it's way faster than accessing to disks. As simple as that. And that's why it takes all the RAM it can if it's not controlled by the max server memory option, but that's another debate.
Now SQL Server, as it's been said before, does not maintain statistics on table variables, and that leads SQL Server to make incorrect cardinality estimations, unless we have a PRIMARY KEY constraint on it, or we add an OPTION (RECOMPILE) query hint at the query that involves such a table.
The reason why Philip is seeing a tremendous performance improvement in switching from table variables to temporary tables is because statistics on the latter exist and are maintained very frequently (6 rows modifications at least and we get a statistics automatic update).
That said, generally programming with table variables and temporary tables is usually a "workaround" to bad queries written because of an incorrect data model.
Whenever we can, we must use CTEs as a better alternative, because then cardinality estimation is possible, and this does not cause contention to allocation pages in TempDB.
Bear in mind that TempDB is a system database, and must be used as such. It is the sandbox of SQL Server for many processes, such as sorts, hashing, row versionning, ...
One last thing : TempDB is optimized since SQL Server 2005 to have a table cache of 32 tables which DDL is different, so that when a table variable is out of use, it does not deallocate all the pages, but leaves one Index Allocation Map page and one data page allocated. That avoids creating "hot points" in allocation pages, because then SQL Server does not need to access all the allocation pages when the workload relies a lot on table variables.
Hope this helps ;)
Posted by Nicolas Souquet
LinkedIn Group: SQL Server Professionals
ReplyDelete@Philip Chin : read SQL Server 2008 Internals by Kalen Delaney. There's a lot in there, and it's a Microsoft Press book.
There's also The Guru's Guide to SQL Server Architecture and Internals, by Ken Henderson.
Both very good books.
Posted by Nicolas Souquet
LinkedIn Group: SQL Server Professionals
ReplyDelete@Philip... Rather than giving a practical example you are giving a psudocode... and Hard disk lights... c'mon what a ridiculous example... lol :)
>> "Side note, I amazed, after more than TEN (10) years this very same debate on temp tables and table variables still rages on. "
... yes I'm amazed that how come u have 10 years of work-x???
>> I did use temp tables years ago, but when table variables came around (I think in SQL Server 2000), I have only used them,
... Ok so now you only use table variables and no temp tables... you really do not know when to use both of them... check @Nicolas comments above.
You really need to read books recommended by @Nicolas.
Posted by Manoj Pandey
LinkedIn Group: SQL Server Professionals
ReplyDelete@Nicolos Souquet:
The definition of ACID is (atomicity, consistency, isolation, durability).
Should not Table Variables act like other '@' like variables in SQL Server?
From a design standpoint of SQL Server, if table variables are persisted on disk, should not other variables that start with a '@' also be persisted on disk?
Should not the fact that it's called a table *variable* mean that it is temporary which is the opposite of the 'D" in ACID, durability. If something bad happens to the server, should not the table variable should be cleared out just like all the other @ variables on REBOOT?
Posted by Philip Chin
LinkedIn Group: SQL Server Professionals
ReplyDelete@Manoj
Once I saw the performance difference between table variables and temp tables, I never looked back.
RULE ONE, at least for me..
Always use table variables for temporary stuff, just like @variables are used for temporary stuff in a stored procedure.
Other RULES for me:
If you need to create table variables, it should be a real need, typically something to simplify, a complex query. Aggregates, sums, or maybe to make a CTE easier to use or give the results in the format I need. A "temporary / intermediate " table *variable* to simplify a future query that can be done on the table variable.
And it should almost never need more than a single simple index.
By the way, I like to follow the KISS - Keep It Simple Stupid philosophy.
Otherwise, if it needs a lot of indexes or even all the statistics, then it must be a complex table and most likely multiple queries are needed to run against it. If so, then why not create a regular table and forget both the temp table and the table variable and then truncate the regular table at the end of the batch or stored procedure. This way you can more easily use Enterprise Manager to debug and inspect and other stuff on what you are doing as the problem was complex, correct?
DO THOSE BOOKS ANSWER THE ORIGINAL QUESTION?
You see those books you mentioned (one published in 2003). If they clearly explained the why's and differences between the temp table and table variables, WHY, after more than 9 years since the first book published in 2003, did the ORIGINAL PERSON POST this QUESTION in the FIRST PLACE to this discussion?
Moreover, if those books are so effective, why did MSDN have to post another blog (see that link mentioned previously dated March 2008) on this same question almost 8 years after table variable were first introduced? In fact, there are so many blogs and discussions on this "table variable vs temp table" it is truly amazing.
See Google "temp table vs table variable" - 220,000 results for this same question? Wow!
http://tinyurl.com/7lddtxr
Posted by Philip Chin
LinkedIn Group: SQL Server Professionals
ReplyDelete@Manoj - "Rather than giving a practical example you are giving a psudocode... and Hard disk lights... c'mon what a ridiculous example... lol :)"
Try it and just fill in the blanks for pseudo code as basically I am creating lots of table variable and temp tables and filing them up via the WHILE loop
I also forgot to add the line to increment the counter
SET x = x +1
so it doesn't endlessly loop.
I don't know why looking at hard drive lights is not a valid test method? Like I said before, I follow the KISS - Keep It Simple Stupid philosophy.
Nevertheless, I guess you can use an IO meter test if you wish, but looking at the hard drive light is more simple.
Posted by Philip Chin
See my answer here that addresses many of the points made in this discussion http://dba.stackexchange.com/q/16385/3690
ReplyDelete