SQL Server Performance Forum – Threads Archive
Maximum row size
What maximum row size means? When I tried to create the following test table,CREATE TABLE [dbo].[tblTest] (
[Col1] [varchar] (20) NOT NULL ,
[ID] [varchar] (10) NOT NULL ,
[Col2] [varchar] (8000) NULL ,
[Col3] [varchar] (8000) NULL ,
) ON [PRIMARY]
GO I get a message that warns me that "the table has been created but its maximum row size (16030) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes." Does this mean that the total bytes of a record shouldn’t exceed 8060 byte?
CanadaDBA
A single record cannot span multiple pages. A single page is 8KB. After subtract some bytes for header and stuff like this, you get 8060 bytes. Refer to Q&A #2 in this <http://support.microsoft.com/kb/260418/en-u> article for more information. Use text datatype instead of varchar to be able to store su much data in your table [dbo].[tblTest]. — Rediscover the web
http://www.mozilla.org/firefox/
SQL server is happy to create a table though a table crosses limit of
8060 bytes per row so far you use
variable data type like varchar/nvarchar but it will throw you an error
while inserting/updating data if actual
row size that a row will occupy goes beyond this limit
quote:Originally posted by FarhadR
What maximum row size means? When I tried to create the following test table,
CREATE TABLE [dbo].[tblTest] (
[Col1] [varchar] (20) NOT NULL ,
[ID] [varchar] (10) NOT NULL ,
[Col2] [varchar] (8000) NULL ,
[Col3] [varchar] (8000) NULL ,
) ON [PRIMARY]
GO I get a message that warns me that "the table has been created but its maximum row size (16030) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes." Does this mean that the total bytes of a record shouldn’t exceed 8060 byte?
CanadaDBA
CREATE TABLE [dbo].[tblTest] (
[Col1] [varchar] (20) NOT NULL ,
[ID] [varchar] (10) NOT NULL ,
[Col2] [varchar] (8000) NULL ,
[Col3] [varchar] (8000) NULL ,
) ON [PRIMARY]
GO I get a message that warns me that "the table has been created but its maximum row size (16030) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes." Does this mean that the total bytes of a record shouldn’t exceed 8060 byte?
CanadaDBA
See if this helps:http://www.sql.co.il/ug/01/Row Size Riddle.ppt
It is a Powerpoint presentation by SQL Server MVP Itzik Ben-Gan and still valid in SQL Server 2000. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
Thanks Frank<br />It is a very good PPT. Now I understand how come the row size limit is 8060. [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
Anyway … There´s one thing that must be considered … I´ve been working here in reducing databases because developers create tables and use overestimated datatypes … when the database is less than 50 GB that´s not a big problem … but in my case we are almost reaching the limits of our disks capacities (4 databases like that + backups !)
Better safe than sorry !
]]>