Varchar(max) problem in SQL server 2005

Question

Post reply

Varchar(max) problem in SQL server 2005

Dipak-387207

Mr or Mrs. 500

Points: 566
More actions
March 4, 2008 at 5:05 pm

#95890

I have one column data type as Varchar(max) and the reason to store as varchar(max) everyday this column value going to be change, some day this column gets 60 character and some day it will get 1000 character , my question is that everytime it is stored as fix length of varchar(max) = 2*31 in SQL server or it will stored depends on the length of column character?

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

The length of the number of characters plus 6 or 8 bytes worth of length info.

Question is, why are you using VARCHAR(MAX) if it'll never get bigger than a thousand bytes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dipak-387207 Mr or Mrs. 500 Points: 566 More actions · Answer 2

Because as per my knowledge VARCHAR(max) stored that much space that is available for each row, if one row have 15 character then it is stored only 2 bytes, and if stored 1000 character so according to that it stores into the the database, so it won't allocate big space, But if we use VARCHAR(1000), means it fix in SQL server space, but in my case every day valued are going to chagne in rows, thats'y i would like use VARCHAR(Max).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Varchar also only stores the characters you enter. A varchar(1000) with 15 characters in will take up 17 bytes in the row (15 for the characters, plus 2 to store the length).

It's char that stores the entire length regardless what's in there. Char(1000) with 15 characters in will take 1000 bytes.

Varchar(max) is a blob data type and is the replacement for TEXT. Only really to be used if you expect the string length to exceed 8000 characters.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

sgivoni SSC-Addicted Points: 491 More actions · Answer 4

GilaMonster (3/4/2008)
Varchar(max) is a blob data type and is the replacement for TEXT. Only really to be used if you expect the string length to exceed 8000 characters.

Not entirely correct - read this article which describes in details how the varchar(max) works.

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html#

A summery of the article:

- if the combined size of the row is no more than 8060 bytes, everything will stay in the row (as in contrast to text) - else the varchar will be placed in another data area

- you can use stringfunctions on varchar(max) like any other varchar - something you can't do on text and blobs

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

sgivoni (3/5/2008)
GilaMonster (3/4/2008)
Varchar(max) is a blob data type and is the replacement for TEXT. Only really to be used if you expect the string length to exceed 8000 characters.
Not entirely correct - read this article which describes in details how the varchar(max) works.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html#
A summery of the article:
- if the combined size of the row is no more than 8060 bytes, everything will stay in the row (as in contrast to text) - else the varchar will be placed in another data area
- you can use stringfunctions on varchar(max) like any other varchar - something you can't do on text and blobs

That may be true (dunno for sure), but I've found that there are performance penalties when using VARCHAR(MAX) compared to the lightweight VARCHAR(8000). They don't seem like much but they do appear to add up... for example, doing a split on a VARCHAR(MAX) with about 7000 characters takes 250 to 485 milliseconds. Doing the same split on VARCHAR(8000) takes 15 milliseconds.

My point is, it's usually better to size things correctly... heh... "Size DOES matter". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Probably depends whether the data for the varchar(max) is in the row, or in separate (blob) pages. If the data's out of row then you'll be incurring extra reads for each row of the table. Could explain the slower results.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 7

My thoughts, exactly... next question would be... when is VARCHAR(MAX) in an "in row" condition? I'm thinking the answer, unlike the TEXT datatype can be forced to be, is "Never".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

I need to confirm this, but I believe it is 'as long as it fits'. Will reseach and confirm or correct later

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

chris.compton SSC Veteran Points: 270 More actions · Answer 9

Jeff Moden (3/5/2008)
... I've found that there are performance penalties... doing a split on a VARCHAR(MAX) with about 7000 characters takes 250 to 485 milliseconds. Doing the same split on VARCHAR(8000) takes 15 milliseconds.
My point is, it's usually better to size things correctly... heh... "Size DOES matter". 😀

I would be interested in hearing about a re-test of the VarChar(7000) vs. VarChar(Max). (which I don't have time to do or I would)

It is my understanding that the largest field is moved out of the 8000 byte codepage first. It would be interesting to me if you re-ran the test after you made sure that there were <8000 bytes in each row (or maybe if it were the only field in the row).

I suspect that it would be much closer to the 15 ms in this case.

Any flaw in my logic? I'm new to SSvr2k5 so thought I'd throw this out there.

P.S. I enjoyed the "size" joke

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 10

I'll see if I can resurrect the code I used tonight and share it here...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 11

there is also a doc (cannot recall right now) wich describes the 8060 pagelimit (fixed datatypes)

it states that if the rowlength for the fixed datatypes + varchar datatypes exceeds 8060 bytes, the varchars will get moved off page. So not only the varchar(max) ones. Its called row-overflow data and adds 24 bytes of overhead to the row and must fitt into the first 8060 bytes !

DBCC IND command provides more info.

found a nice art about it : http://www.sqlservercentral.com/articles/Development/2862/ :w00t:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/12/13/More-undocumented-fun_3A00_-DBCC-IND_2C00_-DBCC-PAGE_2C00_-and-off_2D00_row-columns.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

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

- 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

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 12

chris.compton (3/7/2008)
I would be interested in hearing about a re-test of the VarChar(7000) vs. VarChar(Max). (which I don't have time to do or I would)
It is my understanding that the largest field is moved out of the 8000 byte codepage first. It would be interesting to me if you re-ran the test after you made sure that there were <8000 bytes in each row (or maybe if it were the only field in the row).
I suspect that it would be much closer to the 15 ms in this case.
Any flaw in my logic? I'm new to SSvr2k5 so thought I'd throw this out there.
P.S. I enjoyed the "size" joke

Ok... I found the code I was using for this demonstration. As always, the details are in the comments in the code. Couple of nifty principles in the code, as well. VARCHAR(8000) beats VARCHAR(MAX) every time even though both are given the same length string of over 7800 characters. I'd be interested in seeing what the times are and a brief description of your computer. Run more than once... it only takes a second...

Also note... they've finally fixed the code windows... you can copy and paste directly from the code window to SSMS without loosing any of the indention of end of line markers.

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO #Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE #Tally

ADD PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

GO

--------------------------------------------------------------------------------------------

--===== Local settings and variables

SET NOCOUNT ON

DECLARE @CsvVARCHAR VARCHAR(8000) --Test string for VARCHAR(8000) (VARCHAR variable)

DECLARE @CsvMAX VARCHAR(MAX) --Test string for VARCHAR(MAX) (MAX variable)

DECLARE @BitBucketV VARCHAR(8000) --Take display out of picture on returns for VARCHAR test

DECLARE @BitBucketM VARCHAR(MAX) --Take display out of picture on returns for MAX test

--===== Create a CSV string over 7800 characters long

;WITH cteRand5Digits AS

(--==== Makes 1333 1 to 5 digit numbers and converts to VARCHAR

SELECT TOP (8000/6)

CAST(ABS(CHECKSUM(NEWID())%100000) AS VARCHAR(10)) AS Number

FROM Master.Sys.All_Columns ac1,

Master.Sys.All_Columns ac2

)--==== Concatenates the 1333 numbers into the VARCHAR in variable

SELECT @CsvVARCHAR = ISNULL(@CsvVARCHAR+',' ,'') +Number

FROM cteRand5Digits

--===== Make the MAX variable equal to the VARCHAR variable

SELECT @CsvMax = @CsvVARCHAR

--===== Display a little info on what we just created

PRINT STR(LEN(@CsvVARCHAR)) +' Length of @CsvVARCHAR (actual string is on the next line)'

PRINT @CsvVARCHAR

PRINT ' '

PRINT STR(LEN(@CsvMAX)) +' Length of @CsvMAX (actual string is on the next line)'

PRINT @CsvMAX

PRINT REPLICATE('=',100)

--===== Split the VARCHAR variable and measure the time it takes

PRINT 'Splitting the VARCHAR variable...'

SET STATISTICS TIME ON

SELECT @BitBucketV = SUBSTRING(','+@CsvVARCHAR, t.N+1, CHARINDEX(',', @CsvVARCHAR+',', t.N+1)-t.N)

FROM #Tally t

WHERE SUBSTRING(','+@CsvVARCHAR, t.N, 1) = ','

AND t.N < LEN(','+@CsvVARCHAR)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',100)

--===== Split the MAX variable and measure the time it takes

PRINT 'Splitting the MAX variable...'

SET STATISTICS TIME ON

SELECT @BitBucketM = SUBSTRING(','+@CsvMAX, t.N+1, CHARINDEX(',', @CsvMAX+',', t.N+1)-t.N)

FROM #Tally t

WHERE SUBSTRING(','+@CsvMAX, t.N, 1) = ','

AND t.N < LEN(','+@CsvMAX)

SET STATISTICS TIME OFF

PRINT REPLICATE('=',100)

--===== Housekeeping

DROP TABLE #Tally

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

The only thing I can find on storage of Varchar(max) is that 'SQL decides' (Inside SQL Server 2005: T-sql Programming)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 14

GilaMonster (3/8/2008)
The only thing I can find on storage of Varchar(max) is that 'SQL decides' (Inside SQL Server 2005: T-sql Programming)

Apparently, it decides wrong... a lot! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)