Temp Table Names

  • Comments posted to this topic are about the item Temp Table Names

  • 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 '#%'

  • Thanks Steve!

    😎

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

  • A GLOBAL temporary table has 128 characters as the limit.

  • 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).

  • 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" 😉

    _____________________________________________________________________
    MCSA SQL Server 2012

  • This was removed by the editor as SPAM

  • 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

  • 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.

  • 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....

  • 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.

  • 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. 😛

  • 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

  • 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

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

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