Row Size

Question

Row Size

mithun.gite

SSCrazy

Points: 2333
More actions
April 23, 2009 at 2:49 am

#130213

hi all,
can anyone tell me whats the max row size in 2005?
Mithun

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

8060 bytes.

You can get around that by using one of the MAX data types that are stored off page. Also variable length character data can be pushed off page (row overflow) if it's too large. Both can result in performance egradation.

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

mithun.gite SSCrazy Points: 2333 More actions · Answer 2

this row size me total columns size?

i mean i tried like this first,

create table my table (str1 varchar(3000),

str2 varchar(3000)

str3 varchar(3000)

)

it created the table and i entered 3000 chars in all columns and it working fine.

then tried this

create table my table (str1 varchar(max),

str2 varchar(max)

str3 varchar(max)

)

and i entered 10000 chars in each column and it is also working fine....

so just wanted to clear that row size means the total of all column size?

pls guide me thru, sounds silly but i m not clear...

Mithun

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

create table my table (str1 varchar(3000),
str2 varchar(3000)
str3 varchar(3000)
)
it created the table and i entered 3000 chars in all columns and it working fine.

Yup. Varchar are variable length character data. See my earlier comments about row overflow.

If you try the same thing with char (which is not variable length) you will get an error.

then tried this
create table my table (str1 varchar(max),
str2 varchar(max)
str3 varchar(max)
)
and i entered 10000 chars in each column and it is also working fine....

Yes, as I said, max data types are stored off the page.

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

mithun.gite SSCrazy Points: 2333 More actions · Answer 4

ok means the max row size is 8060 bytes and anything more than then resides off the page , right...

Thanks very much.....

Mithun

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

mithun.gite (4/23/2009)
ok means the max row size is 8060 bytes and anything more than then resides off the page , right...

No.

The max row size is 8060 bytes and creating a row larger than that will give an error.

Variable length columns (varchar, nvarchar, varbinary) can be stored either in the row or off page, depending on the size and hence won't cause errors if they get too large.

Other types will.

CREATE TABLE TooBig (

ID Int,

LargeString CHAR(8000),

SmallerString CHAR(60)

)

Msg 1701, Level 16, State 1, Line 1

Creating or altering table 'TooBig' failed because the minimum row size would be 8071, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

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

mithun.gite SSCrazy Points: 2333 More actions · Answer 6

Clear with the Max row size, it wont allow char to grow more than 8060 and varchar would reside in row upto 8060 and after that off tha page...

thanks a lot......

have a great day!!!

Mithun

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

mithun.gite (4/23/2009)
Clear with the Max row size, it wont allow char to grow more than 8060 and varchar would reside in row upto 8060 and after that off tha page...
thanks a lot......
have a great day!!!
Mithun

Just to emphasize... anything that goes "off row" will create a performance problem compared to things that don't.

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