February 7, 2004 at 5:03 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dasanka/te
My Blog:
February 11, 2004 at 1:29 am
I know that temporary tables are stored in tempdb but where are tables stored in a "table" data type stored?
February 11, 2004 at 4:46 am
David - table variables are stored in memory, if you search in books online under table variables, you will find some help on them. It looks like microsoft would prefer us to use these over temporary tables. See the quote from BOL below:
Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:
Within its scope, a table variable may 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 may not be used in the following statements:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable
statements.
table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.
Measure twice, cut once
February 11, 2004 at 5:47 am
I have found that table variables have their limitations. In a datawarehouse environment, with query results totaling hundreds of thousands of rows, or more, table variables are NOT the way to go. Temp tables work much better.
But.... thank you very much for the rest of the article.
February 11, 2004 at 9:46 am
A common misconception is that table variables are always stored in memory.
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977
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. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
Generally I find that performance is good with table variables that store smaller amts of data. A larger dataset usually runs better in a temp table.
February 11, 2004 at 1:53 pm
Indexing temporary tables is problematic - if you create an index called idxFoo, and 2 users hit the sproc at the same time, then the 2nd user will get an "Index name already exists" type of message. How can you create a unique index name?
Steve
February 12, 2004 at 1:42 am
When you create a temporary table its name is always unique.
You think you have created #Tbl_MyTable and to all intensive purposes you have, but the real name will be #Tbl_MyTable_____...._DE or something similar.
Index names don't have to be unique to the database, though I would regard duplicate named objects to be bad practice.
The indices have to be unique to the object on which they are created.
February 12, 2004 at 1:53 am
So would you just use CREATE INDEX after creating the table ?
Measure twice, cut once
February 16, 2004 at 3:21 pm
What is the reason the article states:
Performance? Readability?
Thanks
Once you understand the BITs, all the pieces come together
February 18, 2004 at 1:36 am
SELECT INTO is slow, horrendously slow on retrieving large recordsets.
Creating the table first, then doing and INSERT INTO is much faster, plus you have greater control over the table structure.
I think SELECT INTO trys to retrieve all data before creating the table and as the data doesn't have anywhere to go until the table is created it simply eats memory. I could be wrong here.
February 11, 2005 at 1:33 am
I use temporary tables to store large results sets for reporting, usually where the values required cannot be calculated in a single SELECT statement. The reporting runs in a third party application with a scripting language which allows me to create and access the temporary tables. Doing this gave my application a huge performance boost (table hints also helped), and helped solved locking problems I was having due to the applications insistance on starting a transaction before running any script code.
However, I have one process which uses a stored procedure for speed, and have used a shared 'permanent' table because temporary tables go out of scope when the stored procedure finishes, as described in this quote from MS Books Online:
"A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table"
I was wondering if a global temporary table would remain in scope after the procedure had completed, it is not clear from the explanation in Books Online.
Alternatively, I might be able to return a table datatype from the procedure, and create a local temporary table from that to drive the report.
Any suggestions welcome!
If it ain't broke, don't fix it...
February 11, 2005 at 2:59 am
Just quick notes about the index for tmp sys table.
it's useless to create index just after tmp table creation. I add index after I fill table with data. Then the data are reindexed and all queries are faster after that.
Based on my experience I can say that using of tmp table is slower compare to use share table. Share table is more complicated with management, but faster. In my case, which is about this range (0-4000 rows and 9 columns(mostly varchar or uniqueidetifier)) I also use sys tmp table for related table to my share table, but there are only three columns. And finaly I use also table datatypes, but there is only one column and max 100 records.
I just don't know how I could solve problem with updating of statistics above this share table due to public role of logged dbuser.
July 14, 2006 at 2:10 am
This article should have been updated prior to re-publish as there are some errors and misconceptions contained within it. Sorry.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 14, 2006 at 2:42 am
I agree with the last reply: This article should be rewritten taking into consideration the comments raised by those involved.
There is no explanantion to justify the statements regarding the limitations of temp tables.
We use a HUGE number of temp tables in our overnight batch jobs and we are constantly looking where temp tables can be replaced by shared permanent table objects. We also saw the benefit of creating indexes on the temp tables and moved the create index statements after the INSERT to improve performance. Does that apply to the CLUSTERED INDEX too? And should we update statistics following insert of a large number of rows?
Last question: Is it worth using TABLOCK on the temp table? It should be unique to that connection so in theory not required?
The article WITH the replies has been very useful. Thanks to all that contributed.
C
C
July 14, 2006 at 2:46 am
Yes you can
Or I sometimes declare it with the create table, e.g.
CREATE TABLE #mytable (mykey int PRIMARY KEY CLUSTERED, mycol char(1))
to let SQL make up the name
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply