SQL Data Type Storage Size/Layout

Max row size in SQL 2000 8060 bytes.
Max row size in SQL 2005 more then 8060 bytes if the table contains Var* or XML columns.  This is due to SQL 2005 Row-Overflow, this will affect performance of the database. ( the Varchar... will be moved to a separate page and a 24-byte pointer will be inserted into the original page.)

SQL database page size is 8KB or 8,000 bytes.
SQL data types    2000, 2005, 2008

char (N) = 1 * N bytes
varchar (N) = 1 * N + 2 bytes
nvarchar (N) = 2 * N + 2 bytes
DateTime = 8 bytes
Float = 8 bytes
ntext/text is being replaced with nvarchar(MAX)/varchar(MAX).  MAX = 2^31 - 1 bytes.
ntext/text is stored in separate page

 

Microsoft SQL Server 7.0 Storage Engine

Inside Microsoft® SQL Server™ 2005: The Storage Engine

Maximum Capacity Specifications for SQL Server 2005

SQL 2005 Fragmentation Information

Comments [0]