InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:53:46 GMT20http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]sandeep.pote (9/23/2008)[/b][hr]We can add constraints on table variable like primary key, unique key and chekc constraints etc ......We cannot create non clustered index on table variable ...[/quote]Just to clarify this statement (again!): Both PKs and UNIQUE constraints are backed by indexes. By default, UNIQUE constraints are backed by nonclustered indexes. So by creating one of these constraints, you are in fact creating a nonclustered index. Therefore, it's fair to say that you can create both clustered and nonclustered indexes on a table variable. Need a non-unique index? No problem, just use an IDENTITY column as a "uniquifier"...[code]DECLARE @n TABLE ( x INT NOT NULL, y INT IDENTITY(1,1) NOT NULL, PRIMARY KEY(x,y))[/code]The IDENTITY column will of course increase the row size, so if you do this and you won't be inserting more than ~64,000 rows, consider using SMALLINT rather than INT (you can seed the IDENTITY with -32,000 to give yourself more room).Wed, 24 Sep 2008 08:15:27 GMTAdam Machanichttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxWe can add constraints on table variable like primary key, unique key and chekc constraints etc.. for eg.:DECLARE @MyTable TABLE( ProductID int primary key IDENTITY(1,1), Price money CHECK(Price < 10.0))We cannot create non clustered index on table variable also we cannot change the declaration of table variable once created i.e. we cannot alter the table variable.Tue, 23 Sep 2008 23:46:38 GMTsandeep.potehttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]foursaints (9/12/2008)[/b][hr]There have already been a couple replies on this so I won't belabor too much, but you definitely can create a PK on a table variable, you just can't name it. You can also create an index and apply any appropriate attributes as well (unique, clustered, nonclustered, etc.). SQL Server won't [i]let [/i]you name these objects for exactly the reason described: possibility of naming collisions.As to the questions about advantages/disadvantages, here's my take: The table varaiable will give you a performance boost if you can keep its contents skinny and short (translation, fewest possible columns and fewest possible rows). From what I've read and heard, SQL Server tries its best to keep the table var's data memory-resident and will only "spill-over" into tempdb if surpasses a certain threshold. It also does help to add an index or two to the table var if you are joining, sorting or grouping on column(s). Don't go hog wild with indexes or you'll make too much work for the engine and very quickly lose any advantage you may have gained. The other performance boost comes with the fact that it is [i]not [/i]a persisted object. Because it is not included in the transaction it does not add write I/O to the log file. And because it automatically falls out of scope when the batch completes it is very quickly and efficiently deleted without executing an additional statement in your code.I've used table var's quite a bit over the past couple years and am very happy with the performance. One of my most common uses is when I need to left join a table but I only need a [b]subset [/b]of its data. I have had very good results when I load the subset into the table var and then left join that in the parent query.Speaking of efficiency, please don't forget about table-valued functions! These puppies are executed in their own memory context and are extremely fast. I think that TVF's are one of the most under-utilized great features of SQL 2000/2005. If you're ever thinking you've got an application for a table variable but wish you could filter its contents, there's your answer.[/quote]Temp tables act the same as table variables when it comes to memory... they, too, are extremely fast when used properly... read Q4/A4 in the following link...... which says...[i]Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database [b]similar to temporary tables[/b]. If memory is available, [b]both table variables and temporary tables are created and processed while in memory (data cache). [/b][/i]Sure, sure... use of a Temp Table normally guarantees a recompile... there's a lot of other things like small changes in data that also guarantee a recompile. Expected lifetime of an execution plan on a busy system is about 5 minutes. AND, table variables are guaranteed to evaluate as a single row table... not always the best thing for an optimal execution plan.Don't use either a Temp Table or Table Variable based on rules of thumb. Do some testing... both will surprise you under different circumstances.Tue, 23 Sep 2008 19:56:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxGr8...thank u for the wounderful article--RijoshTue, 23 Sep 2008 04:38:54 GMTrijosh2joshhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxPKs and UNIQUE constraints are backed by indexes, so you can create indexes, as long as they're unique. Need a non-unique index? Add an identity column and use it as a key to create uniqueness... There are ways around virtually any limitation!Fri, 12 Sep 2008 09:52:21 GMTAdam Machanichttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxWell, this is why I don't post a whole lot, I sometimes type without thinking! You CANNOT execute any DDL for the table var outside of its declaration, therefore you CANNOT create indexes. Inline constraints are the only option for table var's. If you do need indexes, temp tables may be a better option.One other way table vars can increase performance is that they tend to trigger fewer recompiles in your sp's...Fri, 12 Sep 2008 09:50:01 GMTBobSainthttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxThere have already been a couple replies on this so I won't belabor too much, but you definitely can create a PK on a table variable, you just can't name it. You can also create an index and apply any appropriate attributes as well (unique, clustered, nonclustered, etc.). SQL Server won't [i]let [/i]you name these objects for exactly the reason described: possibility of naming collisions.As to the questions about advantages/disadvantages, here's my take: The table varaiable will give you a performance boost if you can keep its contents skinny and short (translation, fewest possible columns and fewest possible rows). From what I've read and heard, SQL Server tries its best to keep the table var's data memory-resident and will only "spill-over" into tempdb if surpasses a certain threshold. It also does help to add an index or two to the table var if you are joining, sorting or grouping on column(s). Don't go hog wild with indexes or you'll make too much work for the engine and very quickly lose any advantage you may have gained. The other performance boost comes with the fact that it is [i]not [/i]a persisted object. Because it is not included in the transaction it does not add write I/O to the log file. And because it automatically falls out of scope when the batch completes it is very quickly and efficiently deleted without executing an additional statement in your code.I've used table var's quite a bit over the past couple years and am very happy with the performance. One of my most common uses is when I need to left join a table but I only need a [b]subset [/b]of its data. I have had very good results when I load the subset into the table var and then left join that in the parent query.Speaking of efficiency, please don't forget about table-valued functions! These puppies are executed in their own memory context and are extremely fast. I think that TVF's are one of the most under-utilized great features of SQL 2000/2005. If you're ever thinking you've got an application for a table variable but wish you could filter its contents, there's your answer.Fri, 12 Sep 2008 09:11:08 GMTBobSainthttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote]Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?[/quote]While both the temp table and the table variable will utilize the TempDB essentially the same way (IE. Memory till table size reaches a certain point), the table variable will not write to the transaction log (which is why it is unaffected by Rollbacks). This can decrease the overall I/O used by an operation. Other consideration include statistics. for a table variable, there is always considered to be 1 row by the optimizer, even if there are tens of thousands, which can result in a less than optimum choice for the join (nested loop vs. hash vs. merge). also, Temp Tables can trigger recompiles of stored procedures that create the object, as the acutal execution plan cannot be persisted (table reference doesn' exist until you are executing), which can also be costly.Wed, 03 Sep 2008 12:26:59 GMTPaul Muharsky-474732http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxBy the way... Folks, say hi to Adam Mechanic... he's the guy that first introduced me to the idea of what I currently call a Tally table.Thanks again for that, Adam.Fri, 18 Jul 2008 20:46:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]Adam Machanic (7/18/2008)[/b][hr][quote][b]Jeff Moden (7/18/2008)[/b][hr][quote][b]Viswanath S. Dhara (7/18/2008)[/b][hr]I am trying to update a table using a correlated subquery. [/quote]Why? Correlated subqueries are a form of RBAR and can actually be worse than a cursor in some cases. Use a correctly formed joined update instead...[/quote]Your "correctly formed" update is non-ANSI Compliant and is nondeterministic in some cases. The query optimizer can--and usually does--optimize out correlated subqueries so that they're not reevaluated on a row-by-row basis. Certainly not worse than a cursor.[/quote]That would be true if there is only 1 column being updated... each correlation in an Update in SQL Server 2000 (haven't checked 2k5, yet) adds about the same amount of time.To be clear, correlated sub-queries [i]can [/i]produce performance thousands of times worse than a cursor depending on the usage and the data... please see the following...[url]http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]Admittedly, that type of correlation won't work in an update and I apologize for confusing the issue...... And, please tell me that your not one of those folks that writes only ANSI compliant code. ;)Fri, 18 Jul 2008 19:44:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxThanks Christian...heh... yeah, I was just wondering if the OP understood that. Very strange sample code to begin with...Fri, 18 Jul 2008 17:27:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]Jeff Moden (7/18/2008)[/b][hr][quote]Again, I wouldn't use a correlated subquery for this. But that's not the big problem here... what does this query do to the @Tab table? Nothing... ID remains the same.[/quote]Hi Jeff,You are correct, I am updating the id with the same value. But since this is only an example to show the syntax, i would not put too much meaning into this.But thanks for your hint.Fri, 18 Jul 2008 11:00:57 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]Jeff Moden (7/18/2008)[/b][hr][quote][b]Viswanath S. Dhara (7/18/2008)[/b][hr]I am trying to update a table using a correlated subquery. [/quote]Why? Correlated subqueries are a form of RBAR and can actually be worse than a cursor in some cases. Use a correctly formed joined update instead...[/quote]Your "correctly formed" update is non-ANSI Compliant and is nondeterministic in some cases. The query optimizer can--and usually does--optimize out correlated subqueries so that they're not reevaluated on a row-by-row basis. Certainly not worse than a cursor.Fri, 18 Jul 2008 08:26:10 GMTAdam Machanichttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]Viswanath S. Dhara (7/18/2008)[/b][hr]I am trying to update a table using a correlated subquery. [/quote]Why? Correlated subqueries are a form of RBAR and can actually be worse than a cursor in some cases. Use a correctly formed joined update instead...[code]--===== Right way to do a "Joined" update UPDATE TableA SET SomeTableAColumn = b.SomeTableBColumn FROM TableB b INNER JOIN TableA a ON a.SomeOtherColumn = b.SomeOtherColumn [/code][quote][b]Christian Buettner (7/18/2008)[/b][hr][quote][b]Viswanath S. Dhara (7/18/2008)[/b][hr]Hi Adam, Your query works, but that is not what I am tring to do!I am trying to update a table using a correlated subquery. If I use a table variable here is the message I get. I can do this with a temp table though. I always prefer them.DECLARE @tab TABLE (id int)INSERT INTO @tab SELECT 10UPDATE @tab SET id = ( SELECT AAPatienNo FROM accountaging WHERE AAPatientNo = @tab.id) Msg 137, Level 15, State 2, Line 3Must declare the scalar variable "@tab".[/quote]Hi Viswanath,try this:[code]DECLARE @tab TABLE (id int)INSERT INTO @tab SELECT 10UPDATE TabSET id = ( SELECT id FROM sysobjects WHERE id = tab.id) FROM @tab Tab [/code][/quote]Again, I wouldn't use a correlated subquery for this. But that's not the big problem here... what does this query do to the @Tab table? Nothing... ID remains the same.Fri, 18 Jul 2008 08:21:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]Viswanath S. Dhara (7/18/2008)[/b][hr]Hi Adam, Your query works, but that is not what I am tring to do!I am trying to update a table using a correlated subquery. If I use a table variable here is the message I get. I can do this with a temp table though. I always prefer them.DECLARE @tab TABLE (id int)INSERT INTO @tab SELECT 10UPDATE @tab SET id = ( SELECT AAPatienNo FROM accountaging WHERE AAPatientNo = @tab.id) Msg 137, Level 15, State 2, Line 3Must declare the scalar variable "@tab".[/quote]Hi Viswanath,try this:[code]DECLARE @tab TABLE (id int)INSERT INTO @tab SELECT 10UPDATE TabSET id = ( SELECT id FROM sysobjects WHERE id = tab.id) FROM @tab Tab [/code]Fri, 18 Jul 2008 04:31:03 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxVery informative article....Fri, 18 Jul 2008 03:20:02 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxone thing I've found in tuning client applications is that table variables when you populate them with a large number of rows, for example 15k and then put them back into a join with maybe 6 real tables can seriously give you query plan issues tending to force scans and making optimisation very difficult - a #temp table doesn't do this. ( it's the stats stuff )Fri, 18 Jul 2008 01:35:06 GMTcolin.Leversuch-Robertshttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxHi Adam, Your query works, but that is not what I am tring to do!I am trying to update a table using a correlated subquery. If I use a table variable here is the message I get. I can do this with a temp table though. I always prefer them.DECLARE @tab TABLE (id int)INSERT INTO @tab SELECT 10UPDATE @tab SET id = ( SELECT AAPatienNo FROM accountaging WHERE AAPatientNo = @tab.id) Msg 137, Level 15, State 2, Line 3Must declare the scalar variable "@tab".Fri, 18 Jul 2008 00:27:08 GMTViswanath S. Dharahttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxTaken all together, a good round up of the differences, but you all just ruined one of my favorite questions for DBA candidates - 'Describe the differences between Temp Tables and Table Variables'. :crazy: Maybe some of them dont read SSC, but then that would be a big point against them anyway ...Thu, 17 Jul 2008 22:04:46 GMTSimon Facerhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]Viswanath S. Dhara (7/17/2008)[/b][hr]Thanks for the Article Roi. There is one more thing you forgot to mention. You can't write a correlated subquery on a table varable where as you can on a temp table. I regularly use that feature in my coding[/quote]Of course you can. You just need to learn to use correlation names.DECLARE @t TABLE (x INT)DECLARE @u TABLE (x INT)SELECT *FROM @t tWHERE EXISTS (SELECT * FROM @u u WHERE u.x = t.x)Thu, 17 Jul 2008 21:54:35 GMTAdam Machanichttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxThanks for the Article Roi. There is one more thing you forgot to mention. You can't write a correlated subquery on a table varable where as you can on a temp table. I regularly use that feature in my codingThu, 17 Jul 2008 21:50:28 GMTViswanath S. Dharahttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxA couple of small corrections: A) INSERT EXEC actually does work on table variables in 2005 and 2008.B) You can create nonclustered indexes on table variables, in the form of UNIQUE constraints.Thu, 17 Jul 2008 15:33:55 GMTAdam Machanichttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxThank you all for your great feedbacks. In this article I tried to illuminate things I didn't find in any other article. In order to find common differences between Variable table to Temp table I added in the beginning of this article 2 links:1. [url=http://support.microsoft.com/default.aspx/kb/305977]Frequently Asked Questions - SQL Server 2000 - Table Variables[/url]2. [url=http://www.sqlservercentral.com/articles/Performance+Tuning/temptabl/662/]Is a Temporary Table Really Necessary? [/url][quote]Is there really any difference or advantage between Temp Tables and Table variables?[/quote][quote]When is it advantageous to use a table variable?[/quote][quote]While there may be a time where it doesn't matter which one you use, is there ever a time where table variables convey an advantage over temp tables? [/quote]Yes. there is a different between those 2 in:- Table variables result in fewer recompilations of a stored procedure as compared to temporary tables- Table variables do not maintain statistics like temporary tables can (good for large tables)- Temp table definition can be altered by alter table - Select into and Insert Exec can be used only with Temp table- Table variables require less locking and logging resources- You can't create non-clustered indexes on table variableMore...As I said, i tried to mention unknown behaviors.Adam Machanic mention few differences (as I wrote above) and he's absolutely right.Anyway, you should pick between them respectively and think of what you need before.I'd like to get feedbacks if you'd like to have an article about all differences between those 2, and give more details then the paragraph above.Thu, 17 Jul 2008 15:29:32 GMTRoi Assahttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]blandry (7/17/2008)[/b][hr]If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?[/quote]Not quite right... Temp Tables and Table Variables both hit memory first.Although very well written, one of the big things missing in this article is that Table Variables do not and cannot be made to use statistics. Depending on what you're trying to do, that can be a huge disadvantage or a huge advantage.The other thing about Table Variables is they die as soon as the run stops... that can make them difficult to troubleshoot because you can't see their contents by doing a simple manual SELECT like you can with Temp Tables... it has to be done in the code and you have to rerun to get the Table Variable to populate every time you want to see it.Thu, 17 Jul 2008 14:15:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxOne big difference not mentioned in the article, but which influences the choice of when to use which, is that autostats do not apply to table variables, but do apply to temp tables. This means that for temp tables, a lot of time can be spent creating statistics, which may or may not be valuable. On the flip side you save that time with table variables, but the same argument applies -- if they were needed you're in trouble, if not you're in better shape. I tend to favor table variables over temp tables A) for smaller sets of data (2-3 data pages, max), a B) for situations in which I'm defining a PK on the table on doing all joins on that key. In the former situation I don't care as much about plan choice because the table is tiny, and in the latter situation statistics generally won't influence plan choice and so are not as necessary.Thu, 17 Jul 2008 13:04:36 GMTAdam Machanichttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxThanks for the great article.Thu, 17 Jul 2008 10:06:19 GMTmojo-168709http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxnice article....2 limitations to add to the list:1) as "sbateman" mentioned, Table Variables cannot be used in SELECT ... INTO statements2) Table Variables cannot be used in sub-processes, such as: EXEC, sp_executesql, a Trigger on a table updated by this proc or batch, or sub-Procedure calls. But Temporary Tables are just fine in these situations.Something to note about performance: I cannot remember where I read this but it was mentioned that Table Variables, not existing as a full table like Temporary Tables do, only ever appear to the Query Optimizier to have one row in them regardless of how many rows really exist. So, as the number of rows increase, the Optimizer is more likely to choose an inappropriate (i.e. inefficient) execution plan--one that is designed for a small set of rows instead of a larger set of rows.Thu, 17 Jul 2008 09:49:39 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote]I personally prefer to use table variables in most situations because...[/quote]Oh ya! I remember now. Thanks!!Thu, 17 Jul 2008 09:42:21 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]blandry (7/17/2008)[/b][hr]Great article, and thanks for it - but under the heading of "the more I learn the less I know"...Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?[/quote]I agree with Blandry, this was a great article.As to advantages and disadvantages, as many people have said sometimes one is better than the other. The ability to create indexes, add columns and use other "Alter Table" commands on a table variable can be handy in certain occasions.I personally prefer to use table variables [b][i]in most situations[/b][/i] because they only exist in the context of the currently running script whereas temp tables exist within your session. This can make a big difference about whether or not I need to worry about dropping temp tables and checking for the existence of temp tables if I am debugging and script and therefore running it over and over with small changes.Thu, 17 Jul 2008 09:31:38 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxI'd like to second BobAtDBS's question. For some reason, I find the syntax for table variables to be more attractive. I started using them as soon as they were available to me. Since I rarely use table variables/temp tables and they have so far only been to hold relatively small amounts of data (with no need for constraints, etc.), it doesn't seem to matter which one I use.While there may be a time where it doesn't matter which one you use, is there ever a time where table variables convey an advantage over temp tables? Why did MS add table variables if there aren't at least some circumstances where there is an advantage?Here's my *guess*: There is a new feature coming out in SQL Server 2008 where you can pass table variables as arguments/parameters to stored procs. Perhaps this only works for table variables and not temp tables? Even if that's true, it doens't explain why they created table variables for SQL Server 2005 (or was it earlier).Thu, 17 Jul 2008 09:17:33 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxGreat article. Short, to the point, and well written.Thu, 17 Jul 2008 07:50:15 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxGood article! Although I have not used table variables much in the past, I have started using them more and will keep in mind data integrity issues going forward.Thu, 17 Jul 2008 07:30:16 GMTcy-dbahttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxon area where I have been back to #table is after CTEeg;WITH cte_OldTraders (TRID, LastSOdate)AS ( SELECT traderid, MAX(orderdate) FROM salesorders WHERE status != N'CANCELLED' GROUP BY traderid )--SELECT customers.id AS xtrid , isNull(LastSOdate,customers.createddate) AS xsdat INTO [b][size="3"]#soldtraders[/size][/b] FROM customersLEFT OUTER JOIN cte_OldTraders ON (customers.id = TRID)WHERE [tradingstatus] = N'CURRENT';--BEGIN TRAN UPDATE traders SET [tradingstatus] = N'DORMANT', [isonhold] = 0 FROM traders INNER JOIN #soldtraders ON (id = xtrid)WHERE [dbo].[xfn_julian_date](xsdat) < 3400 -- aging FunctionCOMMIT TRANI could not get [i][size="3"]@soldtraders[/size][/i] workingso it is GOOD to have knowledge of bothThu, 17 Jul 2008 07:28:52 GMTsbateman-803743http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote][b]sbateman (7/17/2008)[/b][hr]remember you can [b]use[/b] tempdb as a [i]nearly[/i] normal database**I create Structure for XML explict table[s] and error_log table[s] in tempdb and JOB-Agent bcp's these to files.xmlTheir STRUCTURES/Programmes are copied into modelEvery time SQL-2005 is restarted empty tables are created in tempdb,from model, and are then populated as required.this data is available if error is found and correction to 'Standard' database table[s] can be made - otherwise I am not sad to loose-data on sql_shutdown! We have to many tables in standard named as xtemp****** but nobody remembers what they were for!**An article on differences between Tempdb/Model and 'STANDARD' sql-tables is probably some-where, it is just that I have been doing it this way for ages![/quote]Hmmm that makes sense... Grab the data out of tempdb...Thu, 17 Jul 2008 06:46:29 GMTSuperDBA-207096http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxI'll be the one to ask the idiot question:When is it advantageous to use a table variable? Since I started in Version 7, when they didn't exist, I just have never bothered to use one yet. When I need a temp table, I've never felt constrained by it - you can call another SP and let it reference your temp table which is what I thought was the big deal with table variables. You can't pass a table variable into a SP from ADO (now THAT would be cool).Thu, 17 Jul 2008 06:38:22 GMTBobAtDBShttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx[quote]Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?[/quote]One thing that you CAN do with a #temp table that you can NOT do with a @Table variable is to create indexes on columns. As another poster noted, you can create the PK (and thus the PK index) on a @Table variable - but only if it is included in the declare statement. Whereas with a #temp table, you can follow up the create table (or select into) statement with create index statements. This can give the #temp table a big performance benefit, especially if you are loading it with a lot of data.I agree that this is an excellent article... especially the part about the named constraints.Thu, 17 Jul 2008 06:23:44 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxGreat article, and thanks for it - but under the heading of "the more I learn the less I know"...Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?I ask this because we have an app that generates reports by calling stored procedures. So we are only grabbing data when a report is being prepped to run. But as we design stored procs and sometimes play with Temp Tables versus Unions, or Temp Tables versus Table variables, we have yet to see any major significant difference in performance and I am left wondering if these are all just pretty much the same?Thanks again for a great post!Thu, 17 Jul 2008 06:04:01 GMTblandryhttp://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxremember you can [b]use[/b] tempdb as a [i]nearly[/i] normal database**I create Structure for XML explict table[s] and error_log table[s] in tempdb and JOB-Agent bcp's these to files.xmlTheir STRUCTURES/Programmes are copied into modelEvery time SQL-2005 is restarted empty tables are created in tempdb,from model, and are then populated as required.this data is available if error is found and correction to 'Standard' database table[s] can be made - otherwise I am not sad to loose-data on sql_shutdown! We have to many tables in standard named as xtemp****** but nobody remembers what they were for!**An article on differences between Tempdb/Model and 'STANDARD' sql-tables is probably some-where, it is just that I have been doing it this way for ages!Thu, 17 Jul 2008 05:13:13 GMTsbateman-803743http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxNice work! Your article was very informative!Thu, 17 Jul 2008 04:10:23 GMTSuperDBA-207096http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspxHello Roi,you actually can add constraints to table variables.Example:[code]DECLARE @MyTabVar TABLE( PKCol int NOT NULL PRIMARY KEY CLUSTERED ,UniqueCol int NOT NULL UNIQUE)[/code]Thu, 17 Jul 2008 01:49:09 GMTChristian Buettner-167247