| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-05-12 : 14:51:11
|
| If I have multiple VARCHAR(8000) columns what happens when the record size exceeds 8096 (or whatever the threshold is)?Does the table then become the same as if I had used varchar(MAX)? Online rebuild not possible, all those sorts of things? Any other gotchas?I'm just trying to decide whether I should have several varchar(8000) columns which will, 99.9% of the time, collectively be "small", or whether I actually need to do something more clever (e.g. a separate Key/Text table) to prevent ANY rows exceeding 8096 bytes.Thanks :) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-05-12 : 15:26:28
|
As I am sure you have already seen, BOL says: REBUILD WITH ONLINE = ON fails if the table has one or more XML index,Spatial index, large object data type columns: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xmlDoesn't mention what happens if the row is large, but of course that doesn't mean it is OK.However, this quick test ran OK for me:create table TEST_LARGE(Seq int not null identity(1,1),X1 varchar(8000) null,X2 varchar(8000) null,X3 varchar(8000) null,constraint PK_TEST_LARGE primary key clustered (Seq))goinsert TEST_LARGEselect top 5000 X1 = replicate('1',8000), X2 = replicate('2',8000), X3 = replicate('3',8000)from syscolumns a cross join syscolumns bgoalter index PK_TEST_LARGE on TEST_LARGErebuildwith ( online = on )godrop table TEST_LARGECODO ERGO SUM |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 15:47:29
|
| The difference between U-and-ME is the ability to write a clear, concise, test like that. I'm jealous :) Many thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 15:51:36
|
quote:
Originally posted by tkizer
If a row exceeds the size, then the INSERT or UPDATE will fail. You may want to consider breaking it up into multiple tables.
Seems to work OK in SQL2008 (although it definitely fails under SQL 2000 ("row size ... greater than the allowable maximum of 8094.") |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-12 : 15:53:21
|
quote:
Originally posted by Kristen
quote:
Originally posted by tkizer
If a row exceeds the size, then the INSERT or UPDATE will fail. You may want to consider breaking it up into multiple tables.
Seems to work OK in SQL2008 (although it definitely fails under SQL 2000 ("row size ... greater than the allowable maximum of 8094.")
It works on SQL2005 too, so I did a quick google search which said it should fail there too.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 15:54:11
|
| Rhetorical maybe, but you ain't SQL 2008 yet, so you are forgiven! (Although I imagine that SQL2005 was probably the same ... ??) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 15:54:54
|
| Post overlap ... no excuses then, eh? !!!! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-05-12 : 16:04:18
|
I have the advantage of having every version of SQL from 7.0 to 2008 in use to be able to test with.  Think we might even have a 6.5 hidden around somewhere. No 2008 R2 yet (that I know of).CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-05-12 : 16:55:23
|
| From SQL Server 2008 Books Online: Maximum Capacity Specifications for SQL Server "SQL Server 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 Books Online."Probably has some negative impact on performance once the data is pushed out to another page, but if it's a small percentage of rows, it's probably small.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-13 : 02:38:54
|
Thanks MVJ. I had hunted in BoL but not found anything (would have expected it to be linked from VARCHAR descriptions - but maybe I just didn't see it - my wife would tell you that I can't see anything even if its right under my nose  ) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-13 : 02:43:49
|
| "row-overflow data" topic in SQL 2008 BoL is crystal-clear on the matter (including how clustered indexes may fail, and queries becoming synchronous when data overflows etc). Thanks again, would never have thought to look for that "key" in the BoL index. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-05-18 : 12:08:10
|
| hugo.proenca If you have a question, you should start a new thread, instead of posting it on an unrelated thread.Better yet, you can research limits in SQL Server 2008 books Online yourself.CODO ERGO SUM |
|
|
|
hugo.proenca
Starting Member
2 Posts |
Posted - 2010-05-19 : 07:01:13
|
| Sorry for the mistake... Is it possible to remove my question for here? Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Multiple VARCHAR(8000) columns