dearnemo
asked on
Row Size limitation in SQL 2008 R2
SQL.txt
The attched link has my SQL statement. When I try to create table using that SQL I get this warning message:
Warning: The table "PAS_PROJECT_DATA_MINING" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
I know SQL Server engine stores it's data on 8kb pages & a single record must fit in a single page and cannot be spanned to multiple pages as this link says : http://blogs.msdn.com/b/msdnts/archive/2006/12/01/row-size-limitation-in-sql-2000-and-2005.aspx
How can I resolve this issue? TIA.
The attched link has my SQL statement. When I try to create table using that SQL I get this warning message:
Warning: The table "PAS_PROJECT_DATA_MINING" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
I know SQL Server engine stores it's data on 8kb pages & a single record must fit in a single page and cannot be spanned to multiple pages as this link says : http://blogs.msdn.com/b/msdnts/archive/2006/12/01/row-size-limitation-in-sql-2000-and-2005.aspx
How can I resolve this issue? TIA.
alpmoon
It may not be an issue if varchar columns do not usually have long strings at the same row. If it is the case you should use varchar(MAX) for the columns that hold long strings instead of just varchar(..).
You can check that discusion about disadvantages of varchar(max) and make your decisions about varchar vs varchar(max):
http://stackoverflow.com/questions/2091284/varcharmax-everywhere
http://stackoverflow.com/questions/2091284/varcharmax-everywhere
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Dan7el, Thanks for your post. Does this hold true only for SQL Server 2008 R2?
What other steps can I check to make sure I can skip this warning msg?
Thanks.
What other steps can I check to make sure I can skip this warning msg?
Thanks.
That table row is potentially WAY over the 8K limit, like ~28,000 bytes max!
If you're not in SQL 2008, you'll have to normalize the table or split it or something.
If you're not in SQL 2008, you'll have to normalize the table or split it or something.
ASKER
I m using SQL Server 2008. Can you please let me know how did you calculate ~28,000 bytes max ?
If i m using SQL Server 2008 R2, then what considerations should I make for future use?
TIA.
If i m using SQL Server 2008 R2, then what considerations should I make for future use?
TIA.
>> Can you please let me know how did you calculate ~28,000 bytes max ? <<
SELECT SUM(CASE WHEN max_length = -1 THEN 16 ELSE max_length END + 1.5) AS Max_Total_Column_Length
FROM sys.columns
WHERE
object_id = OBJECT_ID('PAS_PROJECT_DAT
SELECT SUM(CASE WHEN max_length = -1 THEN 16 ELSE max_length END + 1.5) AS Max_Total_Column_Length
FROM sys.columns
WHERE
object_id = OBJECT_ID('PAS_PROJECT_DAT
ASKER
@ScottPletcher, Thanks for your reply to my question. I didn't understand case condition inside select statement though. :(
TIA.
TIA.
Max size is 8060 bytes