Regarding using VARCHAR(MAX)

  • Hi All,

    I would like to know whether the usage of VARCHAR(MAX) intead of using VARCHAR(fixedlength) affects the performance.

    Thanks in Advance!!!

    with regards,

    S.N.Surendrababu

  • Varchar(fixedlength) has maximum capacity of 8000. i.e you can define varchar(8000). If you want to store more than this in a varchar column you need to use varchar(max). The maximum capacity of varchar(max) is 2gb.

    Use varchar(max) only when its required.

    check this out for more details -

    http://www.sqlserverandxml.com/2008/01/varcharnvarchar-n-vs-max.html%5B/url%5D

    "Keep Trying"

  • one drawback is that varchar(max) can't form part of a index key.

    also it's simply wasteful and inefficient.

    when you write a program in C, you don't allocate 2gb of memory to hold a 10 character string do you?

    ---------------------------------------
    elsasoft.org

  • True varchar(max) cannot be a part of a index. But it has its uses. Its much easier to store and manipulate large strings compared to sql server 2000.

    "Keep Trying"

  • Hi,

    I use VARCHAR(MAX) for parameters (Not for fields), These parameters are used in the Where clause of the Query.

    for example.

    CREATE PROCEDURE [Sample]

    (

    @deliverycenter varchar(500)

    )

    as

    declare

    select * from itemdetails i where i.deliverycentercd in (@deliverycenter)

    .....

    Here instead of using varchar(500) can I use VARCHAR(Max), does it affects the performance.

  • Hi there,

    If you are sure that the length of the parameter @deliverycenter will be less than 8000 characters then use varchar(500). Whilst varchar(MAX) will not affect the performance much, since they are stored in the database memory space rather than a pointer to data concept as in Sql Server 2000 (TEXT/NTEXT/ IMAGE datatypes). So the process time with varchar(MAX) is proportional to the size of the data you are processing . (Will not be of much difference).

    So the answer to your question is "The performance will not be affected much".

    declare

    select * from itemdetails i where i.deliverycentercd in (@deliverycenter)

    I don't understand why you using the above syntax, instead you can use a direct comparison to compare two strings.(here you are trying to perform a case sensitive compare between @deliverycenter and i.deliverycentercd).

  • Rowsize > 8060 bytes ?

    -table size is still limited by fixed-length types, as well as internal headers and row pointers.

    -You cannot create a table with a row size of greater than 8060 bytes if that table is comprised of e.g. all numeric columns.

    row overflow:

    -If a row exceed 8060 bytes, the data for varying columns (VARCHAR, NVARCHAR, or VARBINARY) will be automatically moved off-row into the large object area.

    In addition, each column that is moved off-row will occupy a 24-byte on-row pointer. Make sure when creating large tables that the pointers will not cause rows to overflow the 8060-byte limit, or data modifications may be terminated as a result of too much data in row.

    If you have off-row columns, you may end up with vast IO overhead.

    (One to read the pointer, the second to read the off-row column)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Ya you are right, that above for 8000 bytes we need a off row data, we should also turn on the option "large value types out of row". But i wonder what is the big difference between the TEXT and VARCHAR(max) apart from the fact that VARCHAR(MAX) can be used as local variables whereas TEXT cannot be so.

  • TEXT is an older datatype, is deprecated in SQL 2005 and will be removed in a future version. Many of the string functions (left, right, substring, replace, etc) don't work on Text, while the do on varchar(max)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The huge advantage of varchar(max) compared to Text is you no longer need to use the special functions to manipulate text-columns, you can use them as variables, parameters, ...

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • of course if you have a large column you should use varchar(max)/nvarchar(max)/varbinary(max) as opposed to text/ntext/image. that wasn't the poster's question though.

    they asked whether you should prefer varchar(int) compared with varchar(max) and the answer is, if you know the data is less than 4000/8000, then use varchar(int)

    about using varchar(max) for parameter types to procs and functions, this post seems to indicate there are perf issues with using the max variant. I have never tried it as I would never bother with the max variants unless needed.

    See Adam Machanic's post here:

    http://www.simple-talk.com/community/forums/thread/1968.aspx

    ---------------------------------------
    elsasoft.org

  • Hi there,

    I checked the bahavior of varchar(max) in Sql Server 2005. I took the following set of statements.

    declare @STR varchar(MAX)

    deckare @replicateCount INT

    set @replicateCount = 100000

    set @STR = REPLICATE ( CAST('abcdefghijklmnopqrs__tuvwxyz' as varchar(MAX)), @replicateCount)

    select PATINDEX('%__tuv%',@str)

    select SUBSTRING(@str,10,20)

    When executing the above code you can notice that the processing time proportionally increases with the value @replicateCount. Another noticable point is that the substring function takes start index as 10 and number of characters as 20 but still the time taken increases with increase in data size.

    Thats why i blogged that performance hit while using the varchar(max) depends on its size.

  • also check...

    http://www.sqlservercentral.com/Forums/Topic464157-145-1.aspx#bm551935

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Here Sorry if I missed something but please clarify me. Varchar mean variable length character and when the length of a character is not defined then we should use varchar.

    Now if I have to create a tablewith a column with varchar datatype, should I take varchar(n) or varchar(max) ? All I have read till now shows the difference with max length of the column. Say If I have the column length with max at around 400-500, what will be the downside of using varchar(max) ?

  • Varchar(max) is for columns with data > 8000 characters. If you have a column with max 500 characters, that's a varchar(500).

    Same reason we don't use bigint to store numbers between 0 and 100, etc.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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