CODEWITHSUNDEEP
sql-serverindexingduplicates
Neilski
Neilski

Reputation: 4415

How do I create a multiple column unique constraint in SQL Server

I have a table that contains, for example, two fields that I want to make unique within the database. For example:

create table Subscriber (
    ID int not null,
    DataSetId int not null,
    Email nvarchar(100) not null,
    ...
)

The ID column is the primary key and both DataSetId and Email are indexed.

What I want to be able to do is prevent the same Email and DataSetId combination appearing in the table or, to put it another way, the Email value must be unique for a given DataSetId.

I tried creating a unique index on the columns

CREATE UNIQUE NONCLUSTERED INDEX IX_Subscriber_Email
ON Subscriber (DataSetId, Email)

but I found that this had quite a significant impact on search times (when searching for an email address for example - there are 1.5 million rows in the table).

Is there a more efficient way of achieving this type of constraint?

Upvotes: 29

Views: 55310

Answers (3)

Ken
Ken

Reputation: 1

You can either

  1. create the index with email address first if you don't search by Data Set id
  2. create two more index one with email and one with data set id.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294257

but I found that this had quite a significant impact on search times (when searching for an email address for example

The index you defined on (DataSetId, Email) cannot be used for searches based on email. If you would create an index with the Email field at the leftmost position, it could be used:

CREATE UNIQUE NONCLUSTERED INDEX IX_Subscriber_Email
   ON Subscriber (Email, DataSetId);

This index would server both as a unique constraint enforcement and as a means to quickly search for an email. This index though cannot be used to quickly search for a specific DataSetId.

The gist of it if is that whenever you define a multikey index, it can be used only for searches in the order of the keys. An index on (A, B, C) can be used to seek values on column A, for searching values on both A and B or to search values on all three columns A, B and C. However it cannot be used to search values on B or on C alone.

Upvotes: 54

yogi
yogi

Reputation: 19591

I assume that only way to enter data into that table is through SPs, If that's the case you can implement some logic in your insert and update SPs to find if the values you are going to insert / update is already exists in that table or not.

Something like this

create proc spInsert
(
    @DataSetId int,
    @Email nvarchar(100)
)
as
begin

if exists (select * from tabaleName where DataSetId = @DataSetId and Email = @Email)
    select -1 -- Duplicacy flag
else
begin
    -- insert logic here
    select 1 -- success flag
end

end
GO


create proc spUpdate
(
   @ID int,
   @DataSetId int,
   @Email nvarchar(100)
)
as
begin

if exists 
(select * from tabaleName where DataSetId = @DataSetId and Email = @Email and ID <> @ID)
    select -1 -- Duplicacy flag
else
begin
    -- insert logic here
    select 1 -- success flag
end

end
GO

Upvotes: -1

Related Questions