Choose only two correct answers?

  • shivasssv123

    Grasshopper

    Points: 12

    Comments posted to this topic are about the item Choose only two correct answers?

  • Vimal Lohani

    SSCommitted

    Points: 1650

    Good basic question.

    Thanks for it.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • Koen Verbeeck

    SSC Guru

    Points: 258942

    Thanks for the question. I didn't know about this limitation. (because who comes up with a 116 character table name?)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hany Helmy

    SSChampion

    Points: 13321

    Koen Verbeeck (8/21/2014)


    Thanks for the question. I didn't know about this limitation. (because who comes up with a 116 character table name?)

    No one 🙂

  • paul.knibbs

    SSCoach

    Points: 15270

    I thought the interesting part was that the actual "name" part of the table name *is* only 116 characters long--apparently, the # showing it's a temporary table is counted as part of the table name? Or am I misinterpreting that?

  • twin.devil

    SSC-Insane

    Points: 22208

    Just for the information.

    Becuase #table names max length is only 116 characters in SQL Server 2008 r2. its also valid of SQL SERVER 2012.

    Nice question. Thanks for sharing

  • Toreador

    SSChampion

    Points: 11232

    I didn't know the answer, but there was only one combination of two answers possible, the two that are basically the same thing but worded differently. Any other combination was contradictory, so I guessed right 🙂

  • rodjkidd

    SSCoach

    Points: 15777

    I think the limit is 128 in reality but to "uniquify" temp tables across multiple connections a number is appended to the end of the name, along with as many _ (underscores) to make it 128 characters. 12 are reserved for the uniquifer!

    I guess if you use code to generate temp table names by appending characters to the end of the name, you can reach this limit fairly easily.

    Rodders...

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • TomThomson

    SSC Guru

    Points: 104772

    paul.knibbs (8/21/2014)


    I thought the interesting part was that the actual "name" part of the table name *is* only 116 characters long--apparently, the # showing it's a temporary table is counted as part of the table name? Or am I misinterpreting that?

    It is part of the name; the wording in BOL (CREATE TABLE) about the table_name argument says "prefixed with" but that's misleading, it should say "beginning with". The BNF text at the top of that BOL page makes it clear that the argument, including the # if any, is the table name, the # isn't some optional additional argument.

    Tom

  • TomThomson

    SSC Guru

    Points: 104772

    twin.devil (8/21/2014)


    Just for the information.

    Becuase #table names max length is only 116 characters in SQL Server 2008 r2. its also valid of SQL SERVER 2012.

    It's valid in every version from SQL Server 2005 to SQL Server 2014 inclusive. Probably in SQL Server 2000 too, but I haven't kept a copy of the transact-sql reference for SQL Server 2000 so I'm only 99.5% certain of that.

    I don't imagine it will change any time soon. It allows more than 5*10**413 local temp table names in each connection, which should be more than enough for anyone, and anyway that number of identifiers would require rather a lot of storage just to hold their names in tempdb - a single connection using that many local temp tables many would use over 5.8*10**415 bytes of storage in tempdb just to hold the names, and that is quite a lot more disc (including drum and solid state) store than has yet been manufactured on our planet, in fact manufacturing it would require the use of more than 10**300 times as much matter as is believed to exist in our universe, let alone on our planet.

    Tom

  • Ed Wagner

    SSC Guru

    Points: 286958

    Nice question, thanks. But I'm with Koen - I've never used a table name that long and I expect to be beaten by someone if I do.

  • SqlMel

    SSCrazy

    Points: 2891

    Good question.

    Thanks.

    ---------------
    Mel. 😎

  • SqlMel

    SSCrazy

    Points: 2891

    paul.knibbs (8/21/2014)


    I thought the interesting part was that the actual "name" part of the table name *is* only 116 characters long--apparently, the # showing it's a temporary table is counted as part of the table name? Or am I misinterpreting that?

    Correct, my friend.

    I'm guessing that was one of the points the author was trying to establish.

    ---------------
    Mel. 😎

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

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