Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Regarding using VARCHAR(MAX) Expand / Collapse
Author
Message
Posted Tuesday, August 12, 2008 10:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 17, 2011 2:24 AM
Points: 4, Visits: 16
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
Post #551587
Posted Tuesday, August 12, 2008 11:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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


"Keep Trying"
Post #551599
Posted Tuesday, August 12, 2008 11:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:06 PM
Points: 471, Visits: 589
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
Post #551600
Posted Tuesday, August 12, 2008 11:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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"
Post #551604
Posted Wednesday, August 13, 2008 12:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 17, 2011 2:24 AM
Points: 4, Visits: 16
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.


Post #551612
Posted Wednesday, August 13, 2008 5:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, May 10, 2009 10:10 AM
Points: 33, Visits: 30
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).
Post #551771
Posted Wednesday, August 13, 2008 6:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #551796
Posted Wednesday, August 13, 2008 6:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, May 10, 2009 10:10 AM
Points: 33, Visits: 30
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.
Post #551817
Posted Wednesday, August 13, 2008 6:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 42,443, Visits: 35,497
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 2008, MVP
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

Post #551834
Posted Wednesday, August 13, 2008 6:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:40 AM
Points: 7,001, Visits: 8,439

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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #551839
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse