How Much Data Can Fit On a Page?
I had an issue recently that was caused by a misunderstanding of SQL Server database design. While it was not a critical issue or one which would cause a user to notice, it was still an issue that affected the corporate website and required time from the development team to solve. I am taking a moment here to explain the issue so everyone is aware of it. Hopefully it will help you here or somewhere else in the future.
About a month ago, a developer and I were talking and I mentioned that we needed to trim the length of some of the tables. Specifically in one instance, we had the following schema for a table.
CREATE TABLE Nav ( [NavID] [int] NOT NULL , [CampgnID] [int] NOT NULL , [categoryid] [varchar] (50) NULL , [NavIDParent] [int] NULL , [NavIDChild] [int] NOT NULL , [NavTxt] [char] (30) NULL , [NavSeq] [tinyint] NOT NULL , [NavURL] [varchar] (8000) NULL , [NavImg] [varchar] (8000) NULL , [NavImgOver] [varchar] (8000) NULL , [NavImgSelected] [varchar] (8000) NULL , [RolloverTip] [varchar] (8000) NULL , [NavImageWidth] [int] NULL , [NavImageHeight] [int] NULL , [NavType] [char] (7) NULL , [LastLevel] [bit] NOT NULL , [DefaultSelect] [bit] NOT NULL )
This table was defined with 5 columns as varchar( 8000). In SQL v7.0, the size of a varchar() field was expanded from a maximum of 255 bytes to 8000 bytes. The size of each data page was also expanded from 2kb to 8kb. Unfortuneately, the requirement of each row to fit on a single page was not changed. The implications of this are that the maximum size of any row is 8kb (8060 bytes after the overhead for the row). If a single column were to contain 8000 bytes, say NavURL, then the total number of bytes that all the other columns together could have is 60. By the way, the total count of bytes of the other columns if each varchar() has 1 byte is about 122. (I think the two bit columns are combined by the server into the same physical byte).
This brings out a misconception that my developer had about the physical storage of data in SQL Server. He thought that you could have any number of columns that could store 8000 bytes in a table. You could implement this, but would have to use image or text datatypes to make this happen. I had been worried that we would encounter a column that would not fit and last night we did. An insert statement was attempting to insert a row that had 8315 bytes which would not fit and caused a summary process to fail.
This could be avoided by watching for the following message after a table creation:
"The total row size (8095) for table 'Nav' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added."
If you see this, then you are taking a chance with your schema that SQL operations may fail due to the size of the row being inserted or updated. I feel that this is poor database design and should be corrected by altering the schema and rethinking the design.
In this case, we reduced the size of each 8000 byte varchar to 1000. This ensured that each row would fit on a row even if the maximum size was used. We did not feel that any data item would exceed 1000, so this was a good comprimise. If there was a significant chance that a data element required 2000 or 3000 bytes, then I would probably add a child table to this one with a row devoted to each large varchar(). We could also consider making the fields text fields, but for the use of this table (URL storage) I would prefer to keep the data in the table row.
I hope this explains the issue clearly to everyone and that it helps you at sometime in the future. If you have questions or comments, please feel free to email me.