Temp Table Names

Question

Temp Table Names

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
April 9, 2015 at 9:53 pm

#100907

Comments posted to this topic are about the item Temp Table Names
April 10, 2015 at 2:44 am

This was removed by the editor as SPAM

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

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

Nathan SSChasing Mays Points: 631 More actions · Answer 1

The system pads the temp table name with underscores and adds a 12 digit uniquifier so that different sessions can use the same temp table name without conflict:

SELECT LEN(name), * FROM tempdb.sys.objects WHERE name LIKE '#%'

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Thanks Steve!

😎

BTW, the leading "#" is included in the count, BOL does not mention that.

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 3

A GLOBAL temporary table has 128 characters as the limit.

peter.row SSCarpal Tunnel Points: 4319 More actions · Answer 4

The script in the answer fails because of the following:

select @a, @b-2, @C

As @C is not declared (nor is it used elsewhere in the script).

Richard Warr SSCertifiable Points: 6966 More actions · Answer 5

There's a good article at Simple Talk[/url] which mentions the limit and the reason.

Where it says "116 characters or less" it means "116 characters or fewer" 😉

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Richard Warr (4/10/2015)
Where it says "116 characters or less" it means "116 characters or fewer" 😉

I would argue that either is correct. If you interpret "116 characters" as a length, then "less" is OK. If you interpret it as a number of characters, it has to be "fewer". It's similar to the age-old debate about the "12 items or less" queue at the supermarket. If you see 12 items as an amount of shopping, rather than a number of items, it's right to say "less".

Anyway, got the question horribly wrong, and thus learnt something - thanks Steve!

John

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Stewart "Arturius" Campbell (4/10/2015)
nimbell (4/10/2015)
The system pads the temp table name with underscores and adds a 12 digit uniquifier so that different sessions can use the same temp table name without conflict:
SELECT LEN(name), * FROM tempdb.sys.objects WHERE name LIKE '#%'
The Uniquifier (and the number of underscores to make the total length (including the #) 128 are appended to the temp table name.
it is, however, due to this uniquifier that the maximun length is 116.

That's exactly it. Wayne Sheffield did a great presentation at a SQL Saturday in Cleveland where he covered this point and so many more, including challenging some of the things we believe about temp tables and table variables. While I already knew much of the material, I don't think anyone could attend that presentation and not learn something new. If you ever get a chance to see the presentation, I'd definitely recommend it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

RLilj33 SSCrazy Points: 2153 More actions · Answer 8

To see a working example of the 116+12 naming in action, see:

http://blog.sqlauthority.com/2012/05/01/sql-server-maximum-allowable-length-of-characters-for-temp-objects-is-116-guest-post-by-balmukund-lakhani/.

robert.diley SSC Eights! Points: 860 More actions · Answer 9

peter.row (4/10/2015)
The script in the answer fails because of the following:
select @a, @b-2, @C
As @C is not declared (nor is it used elsewhere in the script).

Likely @C is a typo and @cmd was intended. 'Course, doesn't change the fact that the code fails....

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 10

Aaargh! I should have gotten that one. I knew that SQL adds the "uniquifier", but I couldn't remember how long it is.

I like the long names that are allowed in SQL Server. When I work in Sybase, the limit is only 30 characters. 🙁

Nice question, Steve, thanks.

sknox SSChampion Points: 12388 More actions · Answer 11

John Mitchell-245523 (4/10/2015)
Richard Warr (4/10/2015)
Where it says "116 characters or less" it means "116 characters or fewer" 😉
I would argue that either is correct. If you interpret "116 characters" as a length, then "less" is OK. If you interpret it as a number of characters, it has to be "fewer". It's similar to the age-old debate about the "12 items or less" queue at the supermarket. If you see 12 items as an amount of shopping, rather than a number of items, it's right to say "less".
Anyway, got the question horribly wrong, and thus learnt something - thanks Steve!
John

Not to derail this thread, but the distinction between "less" and "fewer" is about discrete vs continuous units, not about length vs quantity.

The day you can create a 5 and a half character string, and purchase 2.714 items from the store, I'll agree with you. 😛

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Great explanation. Because BOL said so. :hehe::hehe:

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

sknox (4/10/2015)
John Mitchell-245523 (4/10/2015)
Richard Warr (4/10/2015)
Where it says "116 characters or less" it means "116 characters or fewer" 😉
I would argue that either is correct. If you interpret "116 characters" as a length, then "less" is OK. If you interpret it as a number of characters, it has to be "fewer". It's similar to the age-old debate about the "12 items or less" queue at the supermarket. If you see 12 items as an amount of shopping, rather than a number of items, it's right to say "less".
Anyway, got the question horribly wrong, and thus learnt something - thanks Steve!
John
Not to derail this thread, but the distinction between "less" and "fewer" is about discrete vs continuous units, not about length vs quantity.
The day you can create a 5 and a half character string, and purchase 2.714 items from the store, I'll agree with you. 😛

It's a question of style, and I don't think either is wrong. I'm not saying that it's correct in all cases to use "less" and "fewer" interchangeably, but I'm arguing that it is here. See this blog[/url] from the Oxford Dictionaries website.

John