Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Total size of an index or primary key can not exceed 900 bytes

Tags:

sql-server

When I try to create UK for 7 columns I get

Total size of an index or primary key can not exceed 900 bytes.

How can I solve this error?

like image 658
senzacionale Avatar asked Sep 10 '10 16:09

senzacionale


People also ask

How many bytes is primary key in SQL?

A table can only take one primary key. Primary columns have a maximum length of 900 bytes. A primary key column cannot accept null values.

What is the maximum possible length of an index?

The maximum key length for a nonclustered index is 1700 bytes.

What is the indexed value of a primary key?

A primary key index consists of one or more fields that uniquely identify all records in a table in a predefined order. Because the index field must be unique, the Unique property of the Index object is set to True.


1 Answers

If you are in SSMS you will get this error and will not be able to create the index.

You can however can script it out and the same index creation will only produce a warning.

Warning! The maximum key length is 900 bytes. The index 'IX_Member_LoginName' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

script was

 CREATE NONCLUSTERED INDEX [IX_Members_LoginName] ON [dbo].[Members] 
 (
    [LoginName] ASC 
  )
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
 OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
like image 51
RickWeb Avatar answered Oct 18 '22 13:10

RickWeb