locked
Best practice to define length for varchar field of table in sql server RRS feed

  • Question

  • What is best practice to define length for a varchar field in table

    where field suppose Remarks By Person  varchar(max) or varchar(4000)

    Could it affect on optimization in future????

    experts Reply Must ... 


    Dilip Patil..

    • Moved by Katherine Xiong Thursday, March 13, 2014 5:51 AM more appropriate
    Wednesday, March 12, 2014 5:26 AM

Answers

  • Hi Dilip,

    Varchar(n/max) is a variable-length, non-unicode character data. N defines the string length and can be a value from 1 through 8,000. Max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. We always use varchar when the sizes of the column data entries vary considerably. While if the filed data size might exceed 8,000 bytes in some way, we should use varchar(max).

    So the conclusion is just like Uri said, use varchar(max) or varchar(4000) is depends on how much characters we are going to store.

    The following document about varchar in SQL Server is for your reference:
    http://technet.microsoft.com/en-us/library/ms176089.aspx

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    • Marked as answer by Rishabh Patil Thursday, March 13, 2014 6:50 AM
    Thursday, March 13, 2014 5:51 AM
  • Start with VARCHAR(8000) ...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Rishabh Patil Thursday, March 13, 2014 6:50 AM
    Thursday, March 13, 2014 6:45 AM

All replies

  • It depends on how much characters you are going to store .If you store less than 400 characters there is no need to declare MAX.... 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 12, 2014 6:12 AM
  • Hi Dilip,

    Varchar(n/max) is a variable-length, non-unicode character data. N defines the string length and can be a value from 1 through 8,000. Max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. We always use varchar when the sizes of the column data entries vary considerably. While if the filed data size might exceed 8,000 bytes in some way, we should use varchar(max).

    So the conclusion is just like Uri said, use varchar(max) or varchar(4000) is depends on how much characters we are going to store.

    The following document about varchar in SQL Server is for your reference:
    http://technet.microsoft.com/en-us/library/ms176089.aspx

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    • Marked as answer by Rishabh Patil Thursday, March 13, 2014 6:50 AM
    Thursday, March 13, 2014 5:51 AM
  • Hii Sir...

    I have a parameter suppose, whose varchar length i cant decide

    at that time what shold i write varchar(max) or varchar(8000)


    Dilip Patil..

    Thursday, March 13, 2014 6:05 AM
  • Start with VARCHAR(8000) ...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Rishabh Patil Thursday, March 13, 2014 6:50 AM
    Thursday, March 13, 2014 6:45 AM
  • where does value come from? Is it user input or from another system/process? if latter, then check field definition in source and use the same length here

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, March 13, 2014 7:12 AM