I have several database tables and I need to assure that some columns together always are unique. I currently use a unique constraint like this:
ALTER TABLE [dbo].[MyTable]
ADD CONSTRAINT [AK_MyTable_Unique_Cols]
UNIQUE NONCLUSTERED ([Field_1] ASC, [Field_2] ASC,
[Field_3] ASC, [FkDeliveryId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Field_1 | Field_2 | Field_3 | FkDeliveryId | Sum
Foo | Foo | Bar | 1 | 100
Foo | Bar | Bar | 1 | 900
Bar | Foo | Foo | 1 | 400
Bar | Foo | Bar | 2 | 800 // Not unique
Foo | Foo | Bar | 2 | 600
Bar | Foo | Bar | 2 | 300 // Not unique
The index '' on table 'dbo.MyTable' has 52 columns in the key list.
The maximum limit for index key column list is 16. Could not create
constraint or index. See previous errors.
No the hash won't always be unique. Hash collisions occur.
This seems an odd requirement. Usually the key can be created on a subset of columns in the table.
A generic solution that gets around the 16 column limit would be to create a computed column that concatenates all the columns with some delimiter that is unlikely to occur in the data and then create a unique index on that. Which is basically the same as your linked question suggests.
But there is a 900 byte index key limit overall though. If you need to support arbitrary column lengths including varchar(max) that may well exceed this then you can't do this with declarative constraints and would need some procedural code. Best you can do is create a hash and a non unique index on that and then have your insert process check any hash duplicates to see if they are in fact real duplicates (could happen in a trigger to ensure it is always checked or the ETL process itself - which would likely be more efficient).
If you do it in a trigger it would help to add an identity column to the table. Then the code to identify duplicates would be.
SELECT * FROM Inserted I JOIN BaseTable B ON I.HashValue = B.HashValue AND I.Id<> B.Id /* check remaining columns to see if actual differences exist in null safe way http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx */ AND EXISTS (SELECT B.Col1, B.Col2 INTERSECT SELECT I.Col1, I.Col2)
If the above returns any rows you have a violation and can rollback the transaction.