Why do we use # for temp tables name?

Question

Why do we use # for temp tables name?

cino

SSC Rookie

Points: 42
More actions
June 5, 2011 at 8:23 pm

#239365

I even notice ppl using ## or ###, does this have another meaning?
Is this a common good practice? Or is there other specific good reasons we are using these hexes.
Thanks!
cino

Viewing 5 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323753 More actions · Answer 1

cino (6/5/2011)
I even notice ppl using ## or ###, does this have another meaning?
Is this a common good practice? Or is there other specific good reasons we are using these hexes.
Thanks!
cino

A temp table with a single #sign has limited scope..it exists only for your connection, and only until your connection terminates...a #temp table inside a procedure only exists inside the procedure, and is dropped after the proc completes.

two # signs, like ##Temp, make the table available to all other connections...a global temp table. that table can stick around after your connection is dropped, but is destroyed when all connections on the server disconnect.

more than two # signs is just taking advantage of the fact that the # sign is a valid character for table names...nothing special beyond that. it's a global temp table with extra # in it's name.

best practice is to use single #temp tables, unless you have a specific reason to make data available in a global temp table, but still don't need a permanent table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95461 More actions · Answer 2

Just be careful with global temp tables: as soon as the connection that created it goes away, the global temp table will go away also (as soon as all other connections are on a statement that doesn't use it).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

cino (6/5/2011)
I even notice ppl using ## or ###, does this have another meaning?
Is this a common good practice? Or is there other specific good reasons we are using these hexes.
Thanks!
cino

Using # or ## prefix in temp table name is SQL syntax. There is no alternative to this.

Using ### is bad practice. It confuses the reader (as it did to you)

GSquared SSC Guru Points: 260824 More actions · Answer 4

The other thing to be aware of with global temp tables is not just that they can go away, but also that you can end up with errors if you don't realize one has not yet gone away.

You can get DDL errors when you try to create one that already exists. Even worse, you can get data messed up because you don't realize two or more processes are accessing the same global temp table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon