Andy Warren wrote an article on SQLServerCentral.com the other day about the maximum row size in SQL Server 2005. I don't want to re-hash what Andy already said so you can see the article here: http://www.sqlservercentral.com/articles/Development/2862/.

The gist is that Microsoft built SQL Server to be able to handle rows of data that grow beyond 8060-bytes. It does this by moving certain data off to other pages and leaving behind a 24-byte pointer. Well, after reading Andy's article, I got to thinking, just how many varchar(8000) columns can you stick in a table and fill up before SQL Server fills the page with pointers. I conducted a test.....first off I made a table that contained 400 varchar(8000) columns using the following, now slightly truncated, script:

CREATE TABLE [dbo].[Test](
[BigColumn1] [varchar](8000),
[BigColumn2] [varchar](8000),
[BigColumn3] [varchar](8000),
....

[BigColumn398] [varchar](8000),
[BigColumn399] [varchar](8000),
[BigColumn400] [varchar](8000)
)
GO

Use your imagination, or if you want, a little fill magic in Excel to generate the rest of that create table script. Running this script was not all good, SQL Server did warn me of the impending doom, much like SQL Server 2000 would do if you tried to add more than 1 varchar(8000).

Warning: The table "Test" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

Now on to the fun part, a quick insert statement to get us going:

INSERT INTO Test(
BigColumn1,
BigColumn2,
BigColumn3,

BigColumn398,
BigColumn399,
BigColumn400)
VALUES
(
'TestD',
'TestD',
'TestD',
...

'TestD',
'TestD',
'TestD'
)

And finally the update statement to really mess things up:

UPDATE test SET BigColumn1 = REPLICATE ('0123456789', 800)
UPDATE test SET BigColumn2 = REPLICATE ('0123456789', 800)
UPDATE test SET BigColumn3 = REPLICATE ('0123456789', 800)

...
UPDATE test SET BigColumn272 = REPLICATE ('0123456789', 800)
UPDATE test SET BigColumn273 = REPLICATE ('0123456789', 800)
UPDATE test SET BigColumn274 = REPLICATE ('0123456789', 800)

Well, you may now be asking, why didn't you run all 400 updates? Well, after 273 ran without an error, row number 274 finally died with the old familiar error:

Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8064 which is greater than the allowable maximum of 8060.
The statement has been terminated.

So there you have it, in case you ever had the same thought that I did about how big a row can really be.......now you know, 273 great big columns!

P.S. If you are thinking of designing a 273 column table full of nothing but varchar(8000) columns, please don't. For the love of all that is tech, just stop touching SQL Server and find something else to do with your time.

Eric J - www.cstechcast.com

Digg It