CODEWITHSUNDEEP
sql-server
senzacionale
senzacionale

Reputation: 20916

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

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?

Upvotes: 17

Views: 18092

Answers (3)

RickWeb
RickWeb

Reputation: 2115

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]

Upvotes: 6

Martin Smith
Martin Smith

Reputation: 453426

The question was asked in 2010. SQL Server 2016 has increased the limit to 1700 bytes for nonclustered indexes - so one option could be upgrading.

If there is still a requirement to enforce a unique constraint over the allowed limit see this article for some suggestions of workarounds.

  • CHECKSUM (Possible collisions)
  • Triggers (High Overhead and might fail under snapshot isolation)
  • HASHBYTES (Less chance of collisions but still possible)

Upvotes: 13

Vidar Nordnes
Vidar Nordnes

Reputation: 1364

Other than the obvious (changing the size of these 7 columns), there's no setting or similar. You can however enforce this by using triggers to ensure those 7 columns are unique together.

Upvotes: 1

Related Questions