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 Wednesday, August 13, 2008 8:58 AM
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
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
Posted Wednesday, August 13, 2008 9:22 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,

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
Posted Wednesday, August 13, 2008 2:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 6,997, Visits: 8,410
also check...
http://www.sqlservercentral.com/Forums/Topic464157-145-1.aspx#bm551935


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 #552266
Posted Friday, January 06, 2012 3:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:04 AM
Points: 3,517, Visits: 2,606
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
Posted Friday, January 06, 2012 4:12 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 @ 11:47 AM
Points: 41,525, Visits: 34,442
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 »

Add to briefcase ««12

Permissions Expand / Collapse