Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aex
Yak Posting Veteran
60 Posts |
 Posted - 2010-04-18 : 23:27:55
|
In sql server, 8060 byte is the maximum storage per row in table. But I am quite confuse how it is being calculated.For example, I create the following table:create table MyTable( Column_1 varchar(8000), Column_2 varchar(8000))insert into MyTable values(replicate('0123456789', 800), replicate('0123456789', 800))For every single character stored in varchar column, it is 1 byte rite? If that is the case, the total byte that I've inserted into one row is 16000.Because each row can store up to 8060 bytes of data, so I expect the above query should get some kind of warning or error from sql server, but I didn't get any warning nor error.Any clue on this?aex |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-18 : 23:40:10
|
refer to Maximum Capacity Specifications for SQL Server 2005quote:
SQL Server 2005 supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8 KB" topic in SQL Server 2005 Books Online.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
aex
Yak Posting Veteran
60 Posts |
Posted - 2010-04-19 : 02:17:11
|
| Thanks khtan for the reference url. I will look into it. :)aex |
|
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-04-19 : 04:00:38
|
quote:
A table can contain a maximum of 8,060 bytes per row. In SQL Server 2005, this restriction is relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. The length of each one of these columns must still fall within the limit of 8,000 bytes; however, their combined widths can exceed the 8,060-byte limit. This applies to varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns when they are created and modified, and also to when data is updated or inserted.
Check this for details about "Row-Overflow Data Exceeding 8 KB"SQL Server 2005[url]http://technet.microsoft.com/en-us/library/ms186981(SQL.90).aspx[/url]SQL Server 2008[url]http://technet.microsoft.com/en-us/library/ms186981.aspx[/url] |
|
|
|
|
|
|
|
|
Maximum byte per row?