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

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)

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.


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]

Tags:

Sql Server

Recent Posts