Error: The index entry exceeds the maximum length of 900 bytes

Hi,
while trying to insert I get the error msg. (despite of Microsoft SQL Server
2000 sp3)
"Server: Msg 1946, Level 16, State 4, Line 3
Operation failed. The index entry of length 986 bytes for the index
'IndexName' exceeds the maximum length of 900 bytes."
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280744
The number of entries is about 11400, last id = 11889.
Any ideas? What is a temporary index? Is it possible to extend this limit?
Regards,
BerndBernd,
I suspect you have a varying column (n/varchar or varbinary) indexed. While
you can add these to an index, index rows cannot exceed 900 bytes.
Therefore, if the combined datalength of all columns in your insert that are
included in a single index is > 900, the insert will fail. Try the
following example to see what I mean:
CREATE TABLE #IndexTest(ColA INT, ColB VARCHAR(1000))
GO
CREATE INDEX VARCHARINDEX ON #IndexTest(ColB)
Go
INSERT #IndexTest VALUES (1, REPLICATE('0', 900))
INSERT #IndexTest VALUES (2, REPLICATE('0', 901))
GO
SELECT * FROM #IndexTest
GO
"Bernd Binder" <bernd@.binders.de> wrote in message
news:udocFIRkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> while trying to insert I get the error msg. (despite of Microsoft SQL
Server
> 2000 sp3)
> "Server: Msg 1946, Level 16, State 4, Line 3
> Operation failed. The index entry of length 986 bytes for the index
> 'IndexName' exceeds the maximum length of 900 bytes."
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;280744
> The number of entries is about 11400, last id = 11889.
> Any ideas? What is a temporary index? Is it possible to extend this limit?
> Regards,
> Bernd
>|||FYI - In SQL Server 2005 this restriction is lifted - kind of. The total
length of all key columns in an index cannot exceed 900 bytes but using the
new INCLUDE syntax, you can add extra columns of any length to an index
record - thus creating a query covering index.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e3NOaiRkEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Bernd,
> I suspect you have a varying column (n/varchar or varbinary) indexed.
While
> you can add these to an index, index rows cannot exceed 900 bytes.
> Therefore, if the combined datalength of all columns in your insert that
are
> included in a single index is > 900, the insert will fail. Try the
> following example to see what I mean:
>
> CREATE TABLE #IndexTest(ColA INT, ColB VARCHAR(1000))
> GO
> CREATE INDEX VARCHARINDEX ON #IndexTest(ColB)
> Go
> INSERT #IndexTest VALUES (1, REPLICATE('0', 900))
> INSERT #IndexTest VALUES (2, REPLICATE('0', 901))
> GO
> SELECT * FROM #IndexTest
> GO
>
> "Bernd Binder" <bernd@.binders.de> wrote in message
> news:udocFIRkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > while trying to insert I get the error msg. (despite of Microsoft SQL
> Server
> > 2000 sp3)
> >
> > "Server: Msg 1946, Level 16, State 4, Line 3
> > Operation failed. The index entry of length 986 bytes for the index
> > 'IndexName' exceeds the maximum length of 900 bytes."
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;280744
> >
> > The number of entries is about 11400, last id = 11889.
> >
> > Any ideas? What is a temporary index? Is it possible to extend this
limit?
> >
> > Regards,
> > Bernd
> >
> >
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
Newsbeitrag news:e3NOaiRkEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Bernd,
> I suspect you have a varying column (n/varchar or varbinary) indexed.
While
Yes.
Thank you.
[...]
> >
> > "Server: Msg 1946, Level 16, State 4, Line 3
> > Operation failed. The index entry of length 986 bytes for the index
> > 'IndexName' exceeds the maximum length of 900 bytes."
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;280744
> >
> > The number of entries is about 11400, last id = 11889.
> >
> > Any ideas? What is a temporary index? Is it possible to extend this
limit?
> >
> > Regards,
> > Bernd
> >
> >
>