InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 14:24:09 GMT20http://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]Josh Ashwood (8/9/2011)[/b][hr]Nice article, and definitely on topic for real database developers... However the more relevant question we are often faced with is what performs better - table variables, temp tables, or CTE's or leave the damn thing in one huge query ? An 'It depends' answer would be ok, if expanded on![/quote]Answer : CTE and leave the damn thing in one huge query should be the same because they do the sameTable variables have the advantage that are defined witih the sessionBest performance is with temp tables, which are regular tables stored into the db tempd I want to underline that on temp tables is possible to create indexes and statistics , and this can really improve the performanceWed, 05 Oct 2011 08:48:33 GMTFederico Iorihttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]GilaMonster (8/12/2011)[/b][hr][quote][b]mstjean (8/11/2011)[/b][hr][quote][b]EdSwiedler (8/9/2011)[/b][hr]One thing...(snipped).[/quote]As far as I know...(snipped).[/quote]Automatically named, yes, one column, no...(snipped)...You can create multiple unique constraints in the same way.[/quote][b]Awesome[/b]! Now [u]that [/u]is gonna come in handy!Fri, 12 Aug 2011 10:29:27 GMTmstjeanhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]mstjean (8/11/2011)[/b][hr][quote][b]EdSwiedler (8/9/2011)[/b][hr]One thing that I have found that can cause a temporary table or table variable to perform better than a CTE is that you can create indexes on them. This will help when joining them to other tables in later processing. No, I have not actually captured statistics on this, but have noticed a perceived performance boost. As with everything SQL Server, this will all be impacted by other tasks running at the same time.I will say that I have found that a series of CTE's, each building on the last, does better than the massive join's that I have encountered.[/quote]As far as I know you can only create a single column, automatically-named primary key on a table variable (just learned this pretty recently).[/quote]Automatically named, yes, one column, no.[code="sql"]DECLARE @Test TABLE ( Col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, PRIMARY KEY (col1, col2, col4))[/code]You can create multiple unique constraints in the same way.Fri, 12 Aug 2011 02:18:55 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]EdSwiedler (8/9/2011)[/b][hr]One thing that I have found that can cause a temporary table or table variable to perform better than a CTE is that you can create indexes on them. This will help when joining them to other tables in later processing. No, I have not actually captured statistics on this, but have noticed a perceived performance boost. As with everything SQL Server, this will all be impacted by other tasks running at the same time.I will say that I have found that a series of CTE's, each building on the last, does better than the massive join's that I have encountered.[/quote]As far as I know you can only create a single column, automatically-named primary key on a table variable (just learned this pretty recently).Thu, 11 Aug 2011 17:30:41 GMTmstjeanhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]the sqlist (8/11/2011)[/b][hr][quote][b]the sqlist (8/11/2011)[/b][hr][quote][b]amir.mochtar (8/10/2011)[/b][hr]i created #tmp table when processing large of rows then i made index for some columns, those are often used in inquiry. Did i do right?[/quote]The only problem with the indexes on temp tables in stored procedures or scripts is that they are not given by the SQL engine unique names as in the case of temp tables themselves. So if one connection invokes a procedure or script that creates a temp table and then its indexes, a second connection that will try to do this at the same time will fail because the indexes were already created by the first one. Remember, objects in SQL server has to have unique names.[/quote]I will have to retract that. You actually can create indexes with the same name but on distinct tables.No issues with temp tables indexes.[/quote]yeah, thanks. i got error that you mention earlier, i didn't realize the error will show in second connection, i put in foreachLoop component. i was planning to create hole process in ssis package that will be run in sql agent. by the way, do you know where should i implement begin trans, commit, and rollback in package data flow? if in store procedure, i could easily implement those transaction keyword. Thu, 11 Aug 2011 07:54:02 GMTamir.mochtarhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]the sqlist (8/11/2011)[/b][hr]I will have to retract that. You actually can create indexes with the same name but on distinct tables.No issues with temp tables indexes.[/quote]Index names have to be unique on a table. Constraint names (incl primary key and unique constraints) have to be unique in a database.Thu, 11 Aug 2011 07:32:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]the sqlist (8/11/2011)[/b][hr][quote][b]amir.mochtar (8/10/2011)[/b][hr]i created #tmp table when processing large of rows then i made index for some columns, those are often used in inquiry. Did i do right?[/quote]The only problem with the indexes on temp tables in stored procedures or scripts is that they are not given by the SQL engine unique names as in the case of temp tables themselves. So if one connection invokes a procedure or script that creates a temp table and then its indexes, a second connection that will try to do this at the same time will fail because the indexes were already created by the first one. Remember, objects in SQL server has to have unique names.[/quote]I will have to retract that. You actually can create indexes with the same name but on distinct tables.No issues with temp tables indexes.Thu, 11 Aug 2011 07:17:33 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]amir.mochtar (8/10/2011)[/b][hr]i created #tmp table when processing large of rows then i made index for some columns, those are often used in inquiry. Did i do right?[/quote]The only problem with the indexes on temp tables in stored procedures or scripts is that they are not given by the SQL engine unique names as in the case of temp tables themselves. So if one connection invokes a procedure or script that creates a temp table and then its indexes, a second connection that will try to do this at the same time will fail because the indexes were already created by the first one. Remember, objects in SQL server has to have unique names.Thu, 11 Aug 2011 07:11:24 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxIndexes will definitely help.The only way to know for sure is try it both ways.Wed, 10 Aug 2011 20:02:43 GMTStan Kulp-439977http://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxi created #tmp table when processing large of rows then i made index for some columns, those are often used in inquiry. Did i do right?Wed, 10 Aug 2011 18:57:33 GMTamir.mochtarhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]alen teplitsky (8/10/2011)[/b][hr]could have been bugs in earlier versions of sql 2005 but i used to have problems with temp tables not being deleted. my indexing maintenance was a stored procedure and it would create temp tables for the indexes that needed to be maintained. used to have issues with it failing because the table was already in tempdb. ended up using table variables to fix it[/quote]Most likely the issues were in regards with the indexes unless you used global temp tables. The SQL engine makes sure that a temp table has a unique name even if is created at the same time from multiple connections by adding to the name ______ followed by a unique code. However if the procedure code creates indexes to the temp table the names would not be unique as they are created explicitly as they are specified, if the case.There is no issues with temp tables themselves and never been.Wed, 10 Aug 2011 08:37:57 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxcould have been bugs in earlier versions of sql 2005 but i used to have problems with temp tables not being deleted. my indexing maintenance was a stored procedure and it would create temp tables for the indexes that needed to be maintained. used to have issues with it failing because the table was already in tempdb. ended up using table variables to fix itWed, 10 Aug 2011 08:31:31 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]Patibandla (8/10/2011)[/b][hr]@sqlistBut with select into you cannot have an identity column , you have to use row_number otherwise , which may affect the performance when you are dealing with large number of rows.[/quote]1. That is incorect:select identity(int, 1,1) as qq,* into #tbl from master..sysdatabases2. That doesn't change the fact that there is no difference between the temp table regardless of how you create them.Wed, 10 Aug 2011 08:30:56 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxu got a point thereWed, 10 Aug 2011 08:22:34 GMTGirlhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx@sqlistBut with select into you cannot have an identity column , you have to use row_number otherwise , which may affect the performance when you are dealing with large number of rows.Wed, 10 Aug 2011 07:53:41 GMTPatibandlahttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxBuuuut.. if u r working with a looong sp.. u might want to avoid writting try catch.. bla .. 1 million times :-)Wed, 10 Aug 2011 07:47:01 GMTGirlhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]martha-1063616 (8/10/2011)[/b][hr]Why creating the tables.. sometimes you just do your temptable like this:select distinct CONVERT(nvarchar(30), (KO.a)) AS a, CONVERT(nvarchar(30), (KO.b)) AS b, Navn AS Name into #MyTempTable from Table 1 KOINNER JOIN Table 2 KUON KO.NR = KU.NRYou avoid writting all these creates etc.. and - if it is inside a SP then it gets flushed automatically[/quote]There is no difference whatsoever between a temp table created with teh CREATE TABLE statement or on the fly with SELECT ... INTO. The tempt table gets flushed automatically when the connection created it is closed.Wed, 10 Aug 2011 07:21:14 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxWhy creating the tables.. sometimes you just do your temptable like this:select distinct CONVERT(nvarchar(30), (KO.a)) AS a, CONVERT(nvarchar(30), (KO.b)) AS b, Navn AS Name into #MyTempTable from Table 1 KOINNER JOIN Table 2 KUON KO.NR = KU.NRYou avoid writting all these creates etc.. and - if it is inside a SP then it gets flushed automaticallyWed, 10 Aug 2011 07:17:17 GMTGirlhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxFrom my experience, a temporary table is the only way to go with that many records.A table variable or CTE would be way too slow.Tue, 09 Aug 2011 22:03:33 GMTStan Kulp-439977http://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxCould it possible to use @table variable or CTE for processing 10 million records up? i'm afraid it will drain the memory.Tue, 09 Aug 2011 21:31:14 GMTamir.mochtarhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]Josh Ashwood (8/9/2011)[/b][hr]Nice article, and definitely on topic for real database developers... However the more relevant question we are often faced with is what performs better - table variables, temp tables, or CTE's or leave the damn thing in one huge query ? An 'It depends' answer would be ok, if expanded on![/quote]I'd agree. Showing some examples with finite variables and specific environment described would be helpful.Tue, 09 Aug 2011 09:58:55 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]alen teplitsky (8/9/2011)[/b][hr]probably just me but i try to avoid temp tables as much as i can only because i don't like the I/O overhead of using them. One developer i knew used to use views all the time. i think he went way over board with them because one time i was troubleshooting performance issues with one of his processes and it took me hours to dig into the multiple levels of views that he wrote. most of them only returned a few rows.i don't write a lot of SQL code but end up reading it and most of the really long queries have lots of temp tables. makes it a PITA to find out where the problem is and a lot of times running an estimated execution plan is useless because there is no temp table yet. if there was a view instead of a temp table it would probably make things a lot easiercaveat - like everything in SQL server there is no black or white and different things work in different environments and workloads. temp tables have their uses in some cases[/quote]Outside of improper indexing and scalar UDF usage some of the biggest performance wins I have done for clients is breaking down immensely complex single SELECT statements into interim queries using temporary tables for storage.Oh, and I get paid by the hour, so I go KAAACCHHHIIIIIIINNNGGGG when I see a client with a ton of nested views that I have to wade through!! :cool:Tue, 09 Aug 2011 08:56:37 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote]sometimes the prettier solutions perform far worse[/quote]unfortunately SQL Server cares not one jot for the aesthetics of your codeTue, 09 Aug 2011 08:55:46 GMTben.reesehttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxprobably just me but i try to avoid temp tables as much as i can only because i don't like the I/O overhead of using them. One developer i knew used to use views all the time. i think he went way over board with them because one time i was troubleshooting performance issues with one of his processes and it took me hours to dig into the multiple levels of views that he wrote. most of them only returned a few rows.i don't write a lot of SQL code but end up reading it and most of the really long queries have lots of temp tables. makes it a PITA to find out where the problem is and a lot of times running an estimated execution plan is useless because there is no temp table yet. if there was a view instead of a temp table it would probably make things a lot easiercaveat - like everything in SQL server there is no black or white and different things work in different environments and workloads. temp tables have their uses in some casesTue, 09 Aug 2011 08:02:46 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxOne annoyance about the query optimizer in SQL Server is that sometimes the prettier solutions perform far worse, eg. sometimes ugly procedural T-SQL with temp table use etc can perform far better than a single, succinct SQL SELECT statement using CTE's etc..I think the optimizer could do a better job with CTE definitions in sql server..Or maybe its just my queries :-PTue, 09 Aug 2011 07:59:04 GMTJosh Ashwoodhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx1) The primary if not only reason do break down a complex query into multiple steps with interim storage (using TEMPORARY TABLES, NOT TABLE VARIABLES as discussed in number 2 below) is to ensure optimal performance of said query. You absolutely should NOT break down a single query into multiple steps just for 'human readability'. 2) Table variables should almost NEVER be used. The two exceptions I can think of are if you have a VERY high call situation where recompilations related to temp table activity are causing performance degredation or if you need interim data to remain in place for auditing purposes after a transaction rollback. Others mention performance problems from table variables with lots of rows. Well, I can show you an example that gives bad performance with just a SINGLE row in a table variable.Tue, 09 Aug 2011 07:19:55 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote]A temporary table is available as long as the database connection with which it was executed is still open. If it is declared with the "##" prefix, it is a global variable available to other sessions. If it is declared with the "#" prefix, it is a local variable available only in the current session.[/quote]Worth mentioning here that in the case of global temp tables(##), which actually are kind of rarely used, they remain active until there is at least one open connection that uses them, regardless if it is the one that created it or another. The local temp tables(#) are only available to the connection that created them, of course.Tue, 09 Aug 2011 07:02:53 GMTthe sqlisthttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote]Table variables and CTEs are stored in memory (if sufficient memory is available), while temporary tables are always stored on disk.[/quote]This is untrue, or at best misleading. From [url=http://support.microsoft.com/kb/305977]http://support.microsoft.com/kb/305977[/url]:[quote]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 similar to temporary tables. [i]If memory is available, both table variables and temporary tables are created and processed while in memory (data cache)[/i]. [/quote] (Emphasis added).Several people have written about this. Here's [url=http://sqlserverpedia.com/blog/sql-server-2005/table-variables-are-only-in-memory-fact-or-myth/]one[/url], here's [url=http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/]another[/url].RichTue, 09 Aug 2011 06:57:42 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxA good introduction to the alternatives, but I take exception to the statement that "it is impractical to create new views for every query you write." Certainly, you wouldn't create a new view for EVERY query, just as you won't use Temp Tables or CTEs in EVERY query.For the most part CTEs replace subqueries and potentially make the queries more readable, but don't seem to offer much in the way of performance improvement. They do this by allowing reuse (like a view), but also by keeping the entire query contained. This can be good but also reduces code reuse--a view can be used in more than one query. Their main functional benefit over a view is their ability to use the parameters in a SP. This benefit is shared by a TVF, however there are limitations to what can be done in a function, but again Functions are available for code reuse while CTEs are not.Any design decision will, of course, need to be made in the context of a specific need, balancing code reuse, performance, maintainability, and security.As Always YMMV,--JimFiveTue, 09 Aug 2011 06:45:52 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxIt is a concept to solve complex queriesThanksTue, 09 Aug 2011 06:10:55 GMTkathirvelinfohttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]ben.reese (8/9/2011)[/b][hr]My comment in agreement with what GilaMonster has said, is to be very carful do do proper performance and scale testing if you decide to use table variables, they can perform very badly on large data sets.As an annecdotal example, I have inherited a number of very large and complex queries that use table variables, some of these were taking more than half an hour to run. Doing nothing to them other than changing the table variables to temporary table gets them returning in 30 seconds or so.[/quote]I agree with ben.reese and GilaMonster...I also have run into situations where doing nothing more than moving sub-queries into CTEs or temp tables has increased performance drastically. You definitely need to test each situation as it comes up and just keep in mind that you have several options available.Tue, 09 Aug 2011 06:06:26 GMTGeorge H.http://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxOne thing that I have found that can cause a temporary table or table variable to perform better than a CTE is that you can create indexes on them. This will help when joining them to other tables in later processing. No, I have not actually captured statistics on this, but have noticed a perceived performance boost. As with everything SQL Server, this will all be impacted by other tasks running at the same time.I will say that I have found that a series of CTE's, each building on the last, does better than the massive join's that I have encountered.Tue, 09 Aug 2011 06:02:19 GMTEdSwiedlerhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxThe article is really nice, but its very important to consider the performance before using a table variables, temp tables, or CTE's. In terms of performance I think we should avoid using a temp table both in a stand alone query or in a SP, as the SQL optimizer can't reuse the execution plan for a query/sp with a temp table. It creates a new execution plan each time we reexecute the query/SP. In case of a large SP using a code block with a temp table is very much prone to face a performance drawback because the SQL optimizer is unable to reuse the execution plan each time we reexecute the SP with changed input parameters.However we use tablevariable or CTE for the same requirement which doesn't hamper the performance.Tue, 09 Aug 2011 04:05:33 GMTamarlahonhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxMy comment in agreement with what GilaMonster has said, is to be very carful do do proper performance and scale testing if you decide to use table variables, they can perform very badly on large data sets.As an annecdotal example, I have inherited a number of very large and complex queries that use table variables, some of these were taking more than half an hour to run. Doing nothing to them other than changing the table variables to temporary table gets them returning in 30 seconds or so.Tue, 09 Aug 2011 04:02:50 GMTben.reesehttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspx[quote][b]Josh Ashwood (8/9/2011)[/b][hr]However the more relevant question we are often faced with is what performs better - table variables, temp tables, or CTE's or leave the damn thing in one huge query ? [/quote]It depends. Really.It'll vary with the size and complexity of what you're doing. Simpler queries will probably be better kept in one or with CTEs (if it makes things easier to read). More complex ones may benefit from being split up and run in bits.The major difference that you must realise is that temp tables and table variables are both physical structures - they both store the data in an intermediate state. CTEs are not tables, they're just named subqueries, so they're not splitting out the processing, doing some now some later. It's still a single query.Table variables, because of no statistics, can often be problematic on larger row counts (where anything over a couple hundred is probably large)Tue, 09 Aug 2011 03:20:07 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxI use CTE queries a lot when building datasources for my reports. I prefer them over #temp tables and @tables (because there is more work involved creating those)Once you go slightly overboard with CTEs you'll notice that technique isn't working for a specific problem. A CTE can be running fast, you make a slight change and it runs really really bad. That's where I usually start looking at a CTE &gt; insert that set into a #temp table and finish it off with a SELECT or another CTE against the #temp table.Those occasions are rare though.Nice article, cheers.Tue, 09 Aug 2011 02:58:27 GMTJoffreyhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxI'd echo the above point: would be very interesting to see some analysis of each technique performs.Tue, 09 Aug 2011 02:29:57 GMTAndy Siantonashttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxA very good article , it really enhances the performance just by using the identity columns in #Temp and ##Temp tables rather to using order by while inserting the rows , specifically when you nedd to loop through this data at some later point in the session.Tue, 09 Aug 2011 01:37:42 GMTPatibandlahttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxNice article, and definitely on topic for real database developers... However the more relevant question we are often faced with is what performs better - table variables, temp tables, or CTE's or leave the damn thing in one huge query ? An 'It depends' answer would be ok, if expanded on!Tue, 09 Aug 2011 01:30:45 GMTJosh Ashwoodhttp://www.sqlservercentral.com/Forums/Topic1156511-2674-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/T-SQL/74400/">Simplify Large Queries with Temporary Tables, Table Variables and CTEs</A>[/B]Mon, 08 Aug 2011 22:06:16 GMTStan Kulp-439977