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 2 of 2

««1 2

Regarding using VARCHAR(MAX)

Rate Topic

Display Mode

Topic Options

Author

Message

jezemine

Posted Wednesday, August 13, 2008 8:58 AM

SSC-Addicted

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

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

Post #551996

er.soundararajan-800293

Posted Wednesday, August 13, 2008 9:22 AM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, May 10, 2009 10:10 AM
Points: 33, Visits: 30

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.

Post #552018

ALZDBA

Posted Wednesday, August 13, 2008 2:19 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:13 PM
Points: 6,866, Visits: 8,071

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

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 #552266

sqlnaive

Posted Friday, January 06, 2012 3:47 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 5:26 AM
Points: 2,797, Visits: 1,745

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

Post #1231347

GilaMonster

Posted Friday, January 06, 2012 4:12 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:20 AM
Points: 38,089, Visits: 30,382

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 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 #1231359

« Prev Topic | Next Topic »

15 posts, Page 2 of 2

««1 2

Permissions