In addition to the defined data size, you need to consider row and
column overhead. See the SQL Server 2000 Books Online
<createdb.chm::/cm_8_des_02_92k3.htm> for details on calculating row
size.
SQL Server will return a warning when the max row size may potentially
be exceeded but Enterprise Manager might not report this. I ran the
following script in Query Analyzer and got the warning message.
CREATE TABLE MyTable
(
MyPK bigint NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
MyColumn1 nvarchar(21) NOT NULL,
MyColumn2 nvarchar(4000) NOT NULL
)
If your row length may exceed 8060 bytes, you have a couple of options.
1) Split data into multiple tables:
CREATE TABLE MyTable1
(
MyPK bigint NOT NULL
CONSTRAINT PK_MyTable1 PRIMARY KEY,
MyColumn1 nvarchar(21) NOT NULL
)
CREATE TABLE MyTable2
(
MyPK bigint NOT NULL
CONSTRAINT PK_MyTable2 PRIMARY KEY,
MyColumn2 nvarchar(4000) NOT NULL
)
CREATE View MyTable
AS
SELECT t1.MyPK, t1.MyColumn1, t2.MyColumn2
FROM MyTable1 t1
JOIN MyTable2 t2 ON
t2.MyPK = t1.MyPK
2) Use ntext instead of nvarchar to move the data off of the row:
CREATE TABLE MyTable
(
MyPK bigint NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
MyColumn1 nvarchar(21) NOT NULL,
MyColumn2 ntext NOT NULL
)
EXEC sp_tableoption 'MyTable', 'text in row', 3000
There are additional considerations with ntext. See the Books Online
for more info.
--
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
Quote:> I seem to have hit a bug with a table with an nvarchar
> (4000) column and a couple of other columns totalling 50
> bytes. Although this is less than the allowed 8060 data
> limit and enterprise manager does not complain, when later
> inserting a row with 4000 chars I get the error
> "Cannot create a row of size 8062 which is greater than
> the allowable maximum of 8060"
> The data being inserted does not exceed this limit (it
> should be no more than 8050 bytes). I have found a thread
> on another forum that suggest this occurs when SQL Server
> creates a temp table to hold the inserted data, but does
> anyone have more information and/or a workaround that does
> not involve decreasing the column size?
> TIA,
> Dai
by