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.