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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2007-06-20 : 19:09:27
|
| From what I've read, if a row contains more than 8060 bytes and has varchar(MAX) columns in it, the data in those varchar(MAX) columns will be stored off-row. But what happens if you have two varchar(8000) columns instead and both contain more than 4030 bytes, is the data for both stored off-row? If so, just for that row, or for all rows in that table? And is there ever a good reason to have two varchar(8000) columns in a SQL Server 2005 table, instead of using varchar(MAX)? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-20 : 22:09:10
|
quote:
Originally posted by influent...But what happens if you have two varchar(8000) columns instead and both contain more than 4030 bytes...
You will get an error.CODO ERGO SUM |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2007-06-21 : 02:30:18
|
quote:
Originally posted by Michael Valentine JonesYou will get an error.
No you won't.CREATE TABLE Test (c1 varchar(8000), c2 varchar(8000))INSERT INTO Test SELECT REPLICATE('1', 4031), REPLICATE('2', 4031)works fine. So presumably, yes, it's storing at least one of those strings off-row. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-21 : 08:47:07
|
Sorry, I didn't realize that behavior had changed in SQL 2005.  From BOL:"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 per row; 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."CODO ERGO SUM |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-06-21 : 13:06:42
|
| It occurs to me that they must be storing the varchar data off-row when the total exceeds more than 8060 since allowing the page size limit to be exceeded would basically break everything SQL Server is built around. As far as whether or not it makes sense to ever have two varhcar(8000) columns, that depends on whether varchar(MAX) data is automatically stored off-row even if it's under 8000 characters. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-21 : 14:33:01
|
| Is there an equivalent for varchar(MAX) to the "text in row" for Text - to allow it to be forced in-row if the contents happened to be small enough?Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
|
|
|
|
|
varchar(max) and off-row storage