Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
That's some table. –  Joe Oct 6 '11 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 '11 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. –  AlexKuznetsov Feb 7 '12 at 1:55
add comment

migrated from stackoverflow.com Oct 10 '11 at 10:45

This question came from our site for professional and enthusiast programmers.

6 Answers

up vote 9 down vote accepted

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.

share|improve this answer
    
+1 for the great details - I learned a lot from this answer. Thanks! –  Nick B Oct 7 '11 at 16:33
1  
Watch for the 900 byte index key limit of course... –  gbn Oct 11 '11 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 '11 at 19:43
add comment

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.

share|improve this answer
    
-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 '11 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 '11 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 '11 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 '11 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 '11 at 1:00
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
+1 for the hash suggestion & link. Thank you! –  Nick B Oct 7 '11 at 16:34
add comment

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."
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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