Post reply

Varchar and the data dictionary

  • February 14, 2011 at 1:28 pm

    #233545

    I was wondering whether specifying varchar columns as varchar(255) as a minimum size makes any difference as opposed to varchar(10) for example. varchar(255) is still only one byte in the data dictionary, right?

    I have a habit of specifying 255 unless a business rule prescribes a max length of less than 255.

    Any critique?

  • February 14, 2011 at 1:47 pm

    #1286736

    From a performance standpoint, if you're moving more data, it's slower by that amount, but we're talking trivia most of the time. I just try to find out what the business expects from all fields. You can seriously mess up your reporting if the data isn't what users expect to see. Other than that... nah. Not worth fighting over if you ask me.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 14, 2011 at 7:03 pm

    #1286821

    nico van niekerk (2/14/2011)

    I was wondering whether specifying varchar columns as varchar(255) as a minimum size makes any difference as opposed to varchar(10) for example. varchar(255) is still only one byte in the data dictionary, right?

    I have a habit of specifying 255 unless a business rule prescribes a max length of less than 255.

    Any critique?

    Another than being annoyed by it because it DOES imply the business rules were insufficient, I agree with Grant. Just don't get into the habit of using VARCHAR(MAX) as the norm. THAT can and will cause a performance impact on your code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 20, 2011 at 6:43 pm

    #1289281

    I agree, but I don't think that there is more data to move. If a varchar(255) contains only 10 characters, say, it will only be 10+2 that will be moved. If a varchar(10) contains a full complement, it will still be 10+2, right?

  • February 20, 2011 at 7:05 pm

    #1289283

    nico van niekerk (2/20/2011)

    I agree, but I don't think that there is more data to move. If a varchar(255) contains only 10 characters, say, it will only be 10+2 that will be moved. If a varchar(10) contains a full complement, it will still be 10+2, right?

    Yeah, absolutely, that's how it works. But again, if the business thinks it's only ever going to see 10 characters and it gets 22 or 50 or 243, it can seriously mess up reporting. Clean data is hard enough to get without just tossing the concept of trying to limit the data saved. Again, it's more of a concern for the business and the data for reporting for the business, not so much for performance, although, that can enter into it too, as Jeff pointed out.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 21, 2011 at 3:08 pm

    #1289630

    nico van niekerk (2/20/2011)

    I agree, but I don't think that there is more data to move. If a varchar(255) contains only 10 characters, say, it will only be 10+2 that will be moved. If a varchar(10) contains a full complement, it will still be 10+2, right?

    As I said... it's nothing but an annoyance to me. You'll be safer with the varchar(255). Just don't think for even a minute that the same x+2 rules apply with VARCHAR(MAX) (which was my real point). The reason I bring it up is because I've seen lots'o folks use the same, ummmm.... justification to use VARCHAR(MAX) "just to be safe" and they have no idea of the performance they've lost in the process.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 21, 2011 at 9:02 pm

    #1289674

    Oh, I agree, varchar(max) is totally another beast. My point was that an empty 255 wouldn't cause any increase in disc usage compared to a varchar(10), for example.

    Some folks think using varchar(max) as a declared variable in a SP would give them more than 8K. It doesn't because there is no underlying object in which to spill over when 8K is exceeded. At 8K a varchar(max) variable truncates.

  • February 25, 2011 at 7:54 pm

    #1291873

    nico van niekerk (2/21/2011)

    Some folks think using varchar(max) as a declared variable in a SP would give them more than 8K. It doesn't because there is no underlying object in which to spill over when 8K is exceeded. At 8K a varchar(max) variable truncates.

    I'm really sure that's not true.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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