difference in create temp table; using # vs. tempdb.dbo.table_name
-
can anyone give the advantages / disadvantages of creating temp tables using just the '#' vs using tempdb.dbo.table_name
for example:
create table #table1( test_col int not null )
VS
create table tempdb.dbo.table1( test_col int not null )
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
#Tables are given a unique suffix to make them unique to the process that created them, and are cleaned up automatically when the session ends.
2 or more SQL processes can create a table with the same #TableName and not clobber each other. Nor do they have to handle table dropping/cleanup.
"create table tempdb.dbo.table1" is just the same as creating a table in any other user database, with the exception that the table is removed when the SQL instance is restarted.
-
thanks PW, I'm aware of those, I was looking for performance/memory advantages/disadvantages any ideas?
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
I don't think there are any performance advantages. Any performance advantages will come from proper table structure and indexing. Personally I would advise against explcitly creating your own tables in tempdb. The advantages of the #temp table over a regular table, even one created in tempdb, are all about scope; but nothing you don't already know.
-
Only issue is if you need to apply indexes for performance....can you index (and benefit from an index on) a #table?
-
Andrew -- you can index # tables. I'm asking the question because we had a GURU, who seemed to know everything about MS SQL Server. He created temp table using the "tempdb.dbo.name" and I was wondering why. I will ask him when I get a chance just wanted to know what other people thought.
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
Index away! Below is a little example of how it behaves. In QA, select the option to show the execution plan after execution. Or press ctrl-K and run.
You'll see that the first query seeks the clustered index. The second seeks the 'bat' index since it covers the query. The third query seeks 'bar' and then follows that with a bookmark lookup on the clustered index to get all of the columns it need.
To me, it looks like it behaves just like a regular table with regard to indexes.
hth jg
create table #foo
(a int primary key clustered,
b int,
c int,
d int)
go
create index bat on #foo (b,c)
go
create index bar on #foo (c,d)
go
insert #foo
select 1,2,3,4
union
select 5,6,7,8
select * from #foo where a=1
select c from #foo where b=6
select * from #foo where c=7
drop table #foo
-
Your guru seems self proclaimed. I doubt there's any significant benefit - real or imagined - by using the tempdb.dbo.
syntax. You also lose all of the benefits of temp tables and are instead effectively creating ## global temp tables.
The only possible reason I could think of (and I haven't tried it) is that the tables would hang around after connections had finished using them. Typically you would want your temp table deleted, but if it is a scratch table acting as some sort of staging queue (like a print queue I suppose) then it would be handy to have SQL automatically clobber it when it restarted whilst having the table available to all (everyone has rights to create temp tables via # method - not sure bout direct method you brought up). Having said that, ideally your SQL Server is up for days/weeks at a time, not hours
This could still easily be done in your local DB by having a stored proc create the table if it didn't already exist... -
Normally I would agree with you, I've met some of those "GURU's". This one really knew his stuff. I'll find out if there was a reason and put the post here.
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
I don't know of any reason to try to manually manage tables yourself directly in tempdb. If your guru gives you a reason, I'd love to hear it
As for the question about indexes, yes you can index the heck out of temp tables and you'll get the same benefits that you get from regular tables - except for the fact that you have to re-create that indexes every time you create the temp tables. But same goes for a global temp table as well, in which case you may as well be using a regular table anyway (IMHO). You might be thinking of the limitations imposed on table variables...
-
Most of you know already, but...
The issue is a matter of persistence, indexing and scope. On the subject of persistence, memory tables are dropped when the SPID finishes execution even if the SPID persists. But, # tables and ## tables are dropped only when the SPID is dropped. However, tables explicitly created in tempdb have the same persistence as any other dB, that is, they remain forever until explicitly dropped. The latter has the single exception, as mentioned earlier in this thread, they are dropped when SQL Server restarts because tempdb is purged. I read this last statement somewhere and have not tested it.
As for indexing, memory tables can be indexed on their primary key only, which is a feature that needs to evolve in my opinion. Temp tables of any persistence can be indexed like any other table, and the indexes are of course dropped when the table drops.
Scope is the last issue. Memory tables and # tables have their scope limited to the originating SPID. You cannot even address # tables directly in tempdb using their fully decorated name. Tables expressly created in tempdb, as well as ## tables, can be addressed from any other process as long as the tables persist.
I hope this helps.
...Mel
-
I spoke with the GURU. There was no specific reason for doing this. He just wanted to "code his DDL easier". It was an interesting discussion non the less. Thanks for all the input. Pretty much everything this guy did was to improve performance, I just couldn't figure why he did this. REASON: no reason.
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
I have seen the main difference in temp tables when using DTS Packages. Since DTS tends to split processes into multiple threads, table scope and persistence are both big issues. The # and ## tables should not be used at all, although I have had very limited success. However, the tables explicitly created in tempdb span these two barriers. The advantage is found during manual garbage collection. Although the explicit tables are persistent, they let a DBA know that they can be destroyed on a whim because they exist in tempdb. This garbage collection issue becomes clouded when garbage tables are created in a local db. I found this too often when a database application is supported by allot of manual DTS action and the DTS engineer tends to create local tables, thus causing the space used in the local dB to explode with garbage.
...Mel
-
That's what temp tables are for ...
If you have that many regular tables that you use for intermediate processing and you leave them all hanging around, why not just create your own database to hold them instead of mucking around in system databases that are supposed to be managed by SQL Server itself?
BTW, you keep mentioning "memory tables" and I assume you're talking about Table Variables. If so, calling them "memory tables" is misleading. From Microsoft:
"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)."
-
Thanks, Old Hand, you are right as usual. The true definition of memory tables (if my old memory serves me correctly) are tables that are preloaded when SQL Server initializes. This was something that added to SQL Server in its dawn of which few engineers took advantage. During the dawn of @tables, they were also called memory tables because they were normally used for small amounts of data and thus were resident only in memory.
In the strictest implementation sense of the term, though, even these old initialized tables are not memory tables because they still get managed both in the local dB and get thrashed when they get too big for RAM. They also hog resources, so I have found few implementations remaining and I doubt they are good ones.
I will use the term "memory table" much less in the future. I will file it away with other lost terms like "The Source" and mention them only in terms of nostalgia.
...Mel
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply