8

According to the CREATE INDEX documentation:

Up to 16 columns can be combined into a single composite index key.

We've got a table with ~18 columns that need to form a unique combination. This table is not performance sensitive -- we rarely update values/insert records. We just need to ensure that we avoid duplicating our records... and thought we could impose a simple uniqueness constraint.

Any ideas? I'm open to avoiding the unique index/constraint entirely if there is a better way.

4
  • 5
    That's some table.
    – Joe
    Oct 6, 2011 at 22:55
  • @Joe: not unusual in some circumstances when you have combined similar subtypes into one. In my case, a 15 column key is required instead of 50+ different tables. An implementation decision...
    – gbn
    Oct 11, 2011 at 19:26
  • While what you are asking is possible, I am not so sure it is wise. You are not following the beaten path. As such, you are in for surprises. You are more likely to learn on your own mistakes than on others'. In the long run it might be easier to try a more conventional approach. If you post more details, we could help with the implementation.
    – A-K
    Feb 7, 2012 at 1:55
  • I know it's been awhile, but what prevented you from simply using a GUID identity column? Sep 13, 2016 at 15:05

6 Answers 6

14

Add a persisted computed column that combines the 18 keys, then create an unique index on the computed column:

alter table t add all_keys as c1+c2+c3+...+c18 persisted;
create unique index i18 on t (all_keys);

See Creating Indexes on Computed Columns.

Another approach is to create an indexed view:

create view v 
with schemabinding
as select c1+c2+c3+...+c18 as all_keys
from dbo.t;

create unique clustered index c18 on v(all_keys);

See Creating Indexed Views.

Both approaches allow for a partial key aggregate: aggregate c1+c2+c3 as k1, c4+c5+c6 as k2 etc. then index/create indexed view on (k1, k2, ...). Thia could be beneficial for range scans (index can be used for search on c1+c2+c3.

Of course, all + operation in my example are string aggregation, the actual operator to use depends on the types of all those columns (ie. you may have to use explicit casts).

PS. As unique constraints are enforced by an unique index, any restriction on unique indexes will apply to unique constraints as well:

create table t (
    c1 char(3), c2 char(3), c3 char(3), c4 char(3),
    c5 char(3), c6 char(3), c7 char(3), c8 char(3),
    c9 char(3), c10 char(3), c11 char(3), c12 char(3),
    c13 char(3), c14 char(3), c15 char(3), c16 char(3),
    c17 char(3), c18 char(3), c19 char(3), c20 char(3),
    constraint unq unique
      (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18));
go  


Msg 1904, Level 16, State 1, Line 3
The index '' on table 't' has 18 column names in index key list. 
The maximum limit for index or statistics key column list is 16.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

However, creating the constraint on a persisted computed column works:

create table t (
    c1 char(3), c2 char(3), c3 char(3), c4 char(3),
    c5 char(3), c6 char(3), c7 char(3), c8 char(3),
    c9 char(3), c10 char(3), c11 char(3), c12 char(3),
    c13 char(3), c14 char(3), c15 char(3), c16 char(3),
    c17 char(3), c18 char(3), c19 char(3), c20 char(3),
    all_c as 
        c1+c2+c3+c4+c5+c6+c7+c8+c9+c10+c11+
        c12+c13+c14+c15+c16+c17+c18 
        persisted
        constraint unq unique (all_c));
go  

Obviously, the persisted column consumes the space on disk so the approach may be bad for a very large table. The indexed view approach does not have this problem, it only consumes the space for the index, not the space for the computed column and index.

2
  • 2
    Watch for the 900 byte index key limit of course...
    – gbn
    Oct 11, 2011 at 19:24
  • 1
    @gbn Yes, and that's why I ended up going with the HashBytes function as suggested by RBarryYoung. However, I accepted this answer because it provided more of an explanation and exploration of different methods. (i.e. I learned a lot here)
    – Nick B
    Oct 11, 2011 at 19:43
14

I think that you would do much better to put your unique index check on a computed column that is generated using HASHBYTES('MD5', ...) on the combination of your 18 columns.

0
2

I encountered this problem and my senior DBA suggested using a uniqueness-checking function. My inserts are relatively small and infrequent (~1000 rows, inserted at the beginning of each month) and my only concern is enforcing uniqueness.

CREATE FUNCTION dbo.fn_UQ_table1 ()  
RETURNS BIT

AS
BEGIN
      DECLARE @ResultBit BIT = 1

      IF EXISTS(
      SELECT COUNT(*)
      FROM [table1]
      GROUP BY [c1],[c2],[c3],[c4],[c5],[c6],
            [c7],[c8],[c9],[c10],[c11],[c12],
            [c13],[c14],[c15],[c16]
      HAVING COUNT(*) > 1)
      SELECT @ResultBit = 0

      RETURN      @ResultBit

END

SELECT dbo.fn_UQ_table1()

ALTER TABLE [table1]  
WITH NOCHECK ADD  
CONSTRAINT [CK_UQ] CHECK  (([dbo].[fn_UQ_table1]()=1))

@RBarryYoung, I don't have the rep to comment yet, but I had trouble with the HASHBYTES solution because one of my data types was a datetime, and I committed the newbie(?) mistake of not providing the optional style argument to my CONVERT function when converting to varchar. Without the style, you get the following error when you try to add the PERSISTED UNIQUE NONCLUSTERED constraints:

"column 'key_hash' in table 'table1' cannot be persisted because 
the column is non-deterministic."
0

You could combine some of the values in order to create a new unique value and store that in addition to the current data.

Create a user defined function to create the new values and a trigger to populate the field when data is added, then you don't have much more overhead in maintaing the field.

Combining two or three of your fields would get you under the limit of 16.

5
  • -1 I do not agree with the idea of denormalizing the table for the sake of lowering the number of columns.
    – Matt M
    Oct 10, 2011 at 15:29
  • @Matt M - I'm interested to know why you down voted my answer when it's not too different from the first suggestion in the accepted answer to this question? I'd also like to know why you disagree, what would be your solution?
    – Tony
    Oct 10, 2011 at 15:32
  • Actually, your suggestion is, in fact, different from the accepted solution. You are advocating combining columns, whereas the accepted solution is advocating creating a new column that contains the combined values. Your solution could potentially present performance issues through overly-complex queries to split useful data out of your combined columns. Personally, I would advocate the solution presented by RBarryYoung that utilizes a combined HashBytes PERSISTED computed column placed into a unique index. Conversely, I upvoted his solution.
    – Matt M
    Oct 10, 2011 at 20:04
  • @Matt M - Thanks for your explanation but I did say "... create a new unique value and store that in addition to the current data." I intended the new key column to be a new field complementing the existing data and not replacing it. I agree the use of a persisted calculated field is better than my suggestion of a UDF but, in spirit, my solution was the same.
    – Tony
    Oct 10, 2011 at 22:20
  • It appears that I misread your solution, and I do apologize for that. That having been said, combining a few of the columns is not as good a solution, in my opinion, as the HashBytes solution given. I will retract my -1. Again, I apologize for my reading comprehension.
    – Matt M
    Oct 11, 2011 at 1:00
0

You could go with an trigger for insert/update. Do a select grouping by your columns with a clause of having count(*) > 1. If that comes back non-empty, roll back.

0

Here's what I would do. I'd create an AFTER trigger for INSERT, UPDATE that does a ROW_NUMBER () function, and partitions by all 18 of your unique columns. If the max row number is greater than one, then do a ROLLBACK.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.