SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simplify Large Queries with Temporary Tables, Table Variables and CTEs


Simplify Large Queries with Temporary Tables, Table Variables and CTEs

Author
Message
amir.mochtar
amir.mochtar
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 49
Could it possible to use @table variable or CTE for processing 10 million records up? i'm afraid it will drain the memory.
Stan Kulp-439977
Stan Kulp-439977
SSC Eights!
SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)SSC Eights! (990 reputation)

Group: General Forum Members
Points: 990 Visits: 1127
From 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.
Girl
Girl
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 33
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 KO
INNER JOIN Table 2 KU
ON KO.NR = KU.NR

You avoid writting all these creates etc.. and - if it is inside a SP then it gets flushed automatically
the sqlist
the sqlist
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 724
martha-1063616 (8/10/2011)
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 KO
INNER JOIN Table 2 KU
ON KO.NR = KU.NR

You avoid writting all these creates etc.. and - if it is inside a SP then it gets flushed automatically


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.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist
Girl
Girl
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 33
Buuuut.. if u r working with a looong sp.. u might want to avoid writting try catch.. bla .. 1 million times :-)
Patibandla
Patibandla
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 178
@sqlist

But 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.
Girl
Girl
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 33
u got a point there
the sqlist
the sqlist
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 724
Patibandla (8/10/2011)
@sqlist

But 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.


1. That is incorect:

select identity(int, 1,1) as qq,* into #tbl from master..sysdatabases

2. That doesn't change the fact that there is no difference between the temp table regardless of how you create them.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 4674
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
the sqlist
the sqlist
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 724
alen teplitsky (8/10/2011)
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



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.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search