Create a table called #test, and create a table called test in the tempdb, what’s the difference?

Question

Create a table called #test, and create a table called test in the tempdb, what’s the difference?

ogrishman

SSC Enthusiast

Points: 145
More actions
July 18, 2010 at 1:08 pm

#221363

Hi guys,
In SQL Server 2005(2008 not tested), you can't create a temp function like #function_name, but you can create a functoin called function_name directly in tempdb. Does the function created in this way a temp function? What's the difference between a table called #table_name and the same named table directly created in tempdb?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

The difference would be persistence. The table created in tempdb without the # would persist until the SQL service was restarted. The table created with the # would persist until the connection was terminated.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

DataDog Hall of Fame Points: 3305 More actions · Answer 2

and visibility

#temp is only visible to the current session / connection

temp is visible to all sessions

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Two different connections try to create a table #test and both succeed and get different, independent tables.

Two different connections try to create a table test in TempDB, the first succeeds, the second fails with error object already exists.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 4

Internally, the object with # get a different name:

CREATE TABLE TempDB.dbo.DBs (i int IDENTITY)

SELECT * INTO #dbs

FROM sys.databases

SELECT * FROM TempDB.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%dbs%'

These names look like: #dbs______________________00000000BA48 (some underscores removed). The hex string at the end is what changes.

A session cannot access another session's temp table, although I'd love to know a way of read-only access to it! (Although this is probably not a good idea, as temp tables are transitory and shouldn't really be used by other processes. They also don't really have any security on them either, other than the session ID restriction, so that could be dangerous too.)

Paul White SSC Guru Points: 150467 More actions · Answer 5

ogrishman (7/18/2010)
In SQL Server 2005(2008 not tested), you can't create a temp function like #function_name, but you can create a functoin called function_name directly in tempdb. Does the function created in this way a temp function?

It's true in 2008 and 2008 R2 as well - you can't create a local or global temporary function (with the # or ## prefixes). If you try, you'll get this:

[font="Courier New"].Net SqlClient Data Provider: Msg 1074, Level 15, State 1, Line 1

Creation of temporary functions is not allowed.[/font]

Creating an ordinary function in tempdb is perfectly possible, but it won't behave as you might expect a true temporary object to. It will be visible outside of the current connection, and won't be removed from the database when the connection that created it is terminated. It will of course disappear when tempdb is re-initialized next time the SQL Server restarts, unless it is also defined in the model database.

In some ways, this is a shame, since being able to define a #temporary function might be useful on occasion. There don't appear to be any insurmountable technical reasons why this feature is not implemented (we have temporary stored procedures, for example). Perhaps Microsoft would like to deprecate # and ## temporary objects one day.

Temporary tables and procedures certainly pose some challenges to the server, and exhibit some quite unusual behaviour in some respects. If you are interested, Wayne Sheffield wrote an excellent article for SSC comparing temporary tables with temporary variables: http://www.sqlservercentral.com/articles/66720/

What's the difference between a table called #table_name and the same named table directly created in tempdb?

In case you were wondering what the significance of the hexadecimal digits at the end of the object name (in Jim's post) was all about - it's used to distinguish temporary objects with the same name over different connections. The hexadecimal value is the id of the object in tempdb.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

DataDog Hall of Fame Points: 3305 More actions · Answer 6

you could use

sp_getbindtoken and sp_bindsession

and get access that way

can't see a good reason to though

Paul White SSC Guru Points: 150467 More actions · Answer 7

doobya (7/19/2010)
you could use
sp_getbindtoken and sp_bindsession
and get access that way
can't see a good reason to though

Binding sessions only provides access to an open transaction and to share locks, it won't make a temporary table visible to another connection.

Both procedures mentioned were deprecated in SQL Server 2005.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

DataDog Hall of Fame Points: 3305 More actions · Answer 8

oh yes, good call - I never noticed they were deprecated, I was always fond of those

I don't use component services / mts etc. but

if sp_bindsession is gone that means we are back

to having to use DTC even if there is only one db server

Paul White SSC Guru Points: 150467 More actions · Answer 9

doobya (7/19/2010)
oh yes, good call - I never noticed they were deprecated, I was always fond of those I don't use component services / mts etc. but if sp_bindsession is gone that means we are back
to having to use DTC even if there is only one db server

I must confess I was never a fan - you had to be so careful, especially when modifying data. It always felt a bit shonky to tell the truth.

The received wisdom is that MARS and CLR replace many (local) use cases for binding sessions. I haven't done much with MARS, but the CLR side is pretty neat and very capable (enlisting in the transaction, sharing the execution context).

I'm not sure exactly what you're getting at with the DTC requirement where only one instance is involved. What's an example scenario there? Just interested!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sagar Sawant SSC Eights! Points: 853 More actions · Answer 10

Hi,

1: #test table will allocate resources and it's stored in tempdb.

2: You can alter variable at runtime to #test and also create Clustered and other indexes as well.

3: Use table variable instead of #temp table. The reason is , it's taking less resources as compared to #test table. But you can not alter columns or create clustered indexes on table variable. But you can apply non clustered and unique indexes on table variable.

You can test your #test table query performance on tempdb. Execute below process.

SELECT num_of_reads, num_of_bytes_read, num_of_writes, num_of_bytes_written

FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)

GO

then your #test table query.

GO

SELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_written

FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)

GO

If you executes you will understood the problem of #test means it will allocate resources.

If you declare table variable instead of using #test table. You will get the result i.e. it's not allocating resources in tempdb.

Many Thanks,

Sagar Sawant

Sagar Sawant SSC Eights! Points: 853 More actions · Answer 11

Hi,

1: #test table will allocate resources and it's stored in tempdb.

2: You can alter variable at runtime to #test and also create Clustered and other indexes as well.

3: Use table variable instead of #temp table. The reason is , it's taking less resources as compared to #test table. But you can not alter columns or create clustered indexes on table variable. But you can apply non clustered and unique indexes on table variable.

You can test your #test table query performance on tempdb. Execute below process.

SELECT num_of_reads, num_of_bytes_read, num_of_writes, num_of_bytes_written

FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)

GO

then your #test table query.

GO

SELECT num_of_reads, num_of_bytes_read,num_of_writes, num_of_bytes_written

FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)

GO

If you executes you will understood the problem of #test means it will allocate resources.

If you declare table variable instead of using #test table. You will get the result i.e. it's not allocating resources in tempdb.

Many Thanks,

Sagar Sawant

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Sagar Sawant (7/19/2010)
If you declare table variable instead of using #test table. You will get the result i.e. it's not allocating resources in tempdb.

Common myth, completely wrong.

Temp table and table variable are both allocated space in tempDB, are both added to the tempDB system tables, both are preferably kept in memory but will be spilt to disk if necessary.

See the article that Paul referenced (by Wayne)

3: Use table variable instead of #temp table. The reason is , it's taking less resources as compared to #test table. But you can not alter columns or create clustered indexes on table variable. But you can apply non clustered and unique indexes on table variable.

You cannot create indexes on table variables, clustered or nonclustered. What you can do is, as part of the table's declaration, define a primary key (which by default is enforced by a clustered index) and unique constraints (which are by default enforced by nonclustered indexes)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 13

Here's the link again, Sagar:

http://www.sqlservercentral.com/articles/66720/

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

DataDog Hall of Fame Points: 3305 More actions · Answer 14

I'm not sure exactly what you're getting at with the DTC requirement where only one instance is involved. What's an example scenario there? Just interested!

I have only dabbled in this stuff

But for a long time if you wanted to use serviced components (whether VB6 or .NET)

you had to use DTC - that was the only way multiple components (which could be running on different servers)

could enlist in the same transaction

The way it was meant to be used was:

multiple instances of multiple components on multiple servers

enlisting in transactions across multiple database servers

But there was an improvement made to .NET serviced components which would use sp_bindsession instead of DTC

to enlist the components

Which meant if you had a small setup (N app server(s) + one db server) you could avoid DTC completely

the benefits being less setup work, less management hassle, better performance and the ability to avoid

the mandatory serializable isolation (iirc)

Which was good for me because I intended to avoid relying on DTC for as long as possible

preferring to scale out in such a way that I never need it, and so far have never used it in production 🙂