Regarding using VARCHAR(MAX)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Regarding using VARCHAR(MAX)

15 posts, Page 1 of 2

1 2 »»

Regarding using VARCHAR(MAX)

Rate Topic

Display Mode

Topic Options

Author

Message

surendra.babu

Posted Tuesday, August 12, 2008 10:39 PM

Forum 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

ChiragNS

Posted Tuesday, August 12, 2008 11:17 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

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

jezemine

Posted Tuesday, August 12, 2008 11:23 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 9:54 AM
Points: 470, Visits: 588

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

ChiragNS

Posted Tuesday, August 12, 2008 11:35 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

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

surendra.babu

Posted Wednesday, August 13, 2008 12:00 AM

Forum 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

er.soundararajan-800293

Posted Wednesday, August 13, 2008 5:29 AM

SSC 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

ALZDBA

Posted Wednesday, August 13, 2008 6:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 6,861, Visits: 8,047

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

     Jul 13

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

- 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" BigGrin

Need a bit of Powershell? How about this

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

Post #551796

er.soundararajan-800293

Posted Wednesday, August 13, 2008 6:34 AM

SSC 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

GilaMonster

Posted Wednesday, August 13, 2008 6:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671, Visits: 29,925

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

ALZDBA

Posted Wednesday, August 13, 2008 6:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 6,861, Visits: 8,047

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

     Jul 13

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

Need a bit of Powershell? How about this

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

Post #551839

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions