Post reply

data type

  • March 10, 2009 at 2:04 am

    #126864

    Hi,

    I need to have two varchar-fields in a table, one is around 2000 charcters long, other is around 5000 charcters long. I am not sure about whether to choose varchar(max) field instead varchar(5000) field, please advise me on what to choose and the pros and cons?

    Thanks in advanvce.

    Awp

  • March 10, 2009 at 2:13 am

    #956612

    I never used varchar(max), but this seems to be a nice feature in SQL2005.

    This is from BOL:

    Use char when the sizes of the column data entries are consistent.

    Use varchar when the sizes of the column data entries vary considerably.

    Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • March 10, 2009 at 5:50 am

    #956689

    If you use the 2000 and the 5000 field together, you're going to get warnings and potentially errors because you'll exceed the page length limit. MAX bypasses that issue. I'd recommend using it for both fields. It doesn't cost you excessive reads when the value is stored on the page when it is small enough. I wouldn't use it for everything, just because though. I'd keep appropriate limits in place, but when you get to really big fields, it makes sense.

    "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

  • March 10, 2009 at 1:31 pm

    #957042

    Thanks for the replies, it was really helpful, however what I can't understand is when I define the fields with fixed lengths, which is less than 8000-that is the maximum, as varchar(5000) and varchar(2000) how I could get the warnings.

    Also varchar(max) stores the data differently compared to other types, is it better to save the data in fixed lengths varchar as it may be an advantage in terms of space and performance - or it doesn't matter?

    Thanks,

    Awp

  • March 10, 2009 at 4:00 pm

    #957128

    Hi

    It just doesn't matter if you use the MAX option or the 5000 and 2000 chars option. As usual the best way is to keep the row data less than 8kb but if you need it, use it :).

    Greets

    Flo

  • March 10, 2009 at 7:04 pm

    #957203

    5 + 2 = 7 not 8... Yeah, that simple math gets away from me sometimes. Sorry about that.

    "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

  • March 10, 2009 at 7:32 pm

    #957210

    As Flo implied, variable length columns in SQL Server 2005 are handled quite a bit differently than they were in SQL Server 2000. For example, the following would cause the ol' "exceeded 8060 bytes, yada-yada" warning, but SQL Server 2005 takes it all in stride without so much as a blip...

    CREATE TABLE #WideOne

    (

    ColA VARCHAR(8000),

    ColB VARCHAR(8000),

    ColC VARCHAR(8000),

    ColD VARCHAR(8000),

    ColE VARCHAR(8000)

    )

    Well.... kinda... anytime you have to go "outside the page", you will get some reduction in performance.

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

  • March 11, 2009 at 10:24 am

    #957647

    Thank you all for all your responses, it has been very helpful.

    Awp

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

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