T-SQL Unique Index - classification of unique - advice needed

Question

T-SQL Unique Index - classification of unique - advice needed

aaron.reese

SSChampion

Points: 13498
More actions
August 17, 2021 at 9:14 am

#3920664

I have inherited a data warehouse with Type II SCD tables. Each row has an entity_identifier (not null), StartDate (not null), EndDate (nullable) and then the entity attributes. When an attribute changes, the EndDate is completed and a new record added with the new StartDate. So far - So good
Each table also has a Unique index of entity_identifier with all the attribute fields in the included columns (Start and End Date are not in the covering index)
I didn't think this was possible, I though the uniqueness needs to be on the index fields, are the covering fields included in the uniqueness constraint? If so would I not get insert errors if an attribute value was reverted to a previous state (e.g from Status:pending to Status:active then back to Status:pending)

NOTE: Cross posted at StackOverflow

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

You can define a define a UNIQUE CONSTRAINT completely separately to your other indexes, and primary key. And no, if a column isn't included in the definition of the UNIQUE CONSTRAINT it isn't considered as part of it. In your description, you state that the columns StartDate and EndDate aren't defined within the UNIQUE CONSTRAINT so they wouldn't be considered; only the columns within the constraints definition are.

We don't have any sample data, nor any sample DDL here, but here's a quick example. Let's take a table with the following definition:

CREATE TABLE dbo.SomeTable (UniqueID int IDENTITY(1,1),
                            RepeatableID int NOT NULL,
                            StartDate date NOT NULL,
                            EndDate date NUll,
                            SomeColumn varchar(20) NULL);

And then let's create a UNIQUE CONSTRAINT on RepeatableID and SomeColumn:

ALTER TABLE dbo.SomeTable ADD CONSTRAINT UQ_RepeatableID_SomeColumn UNIQUE (RepeatableID,SomeColumn);

Now, if I wanted, I could INSERT the follow rows, and they would all be permitted:

INSERT INTO dbo.SomeTable (RepeatableID, StartDate, EndDate, SomeColumn)
VALUES(1,'20210817',NULL,NULL),
      (1,'20210817',NULL,'Apples'),
      (1,'20210817','20210821','Cars');

If, however, I tried to INSERT the following row, it would fail, because a row already exists where the value of RepeatableID is 1 and the value of SomeColumn is NULL. It doesnt' matter that the values of StartDate and EndDate are completely different.

INSERT INTO dbo.SomeTable (RepeatableID, StartDate, EndDate, SomeColumn)
VALUES(1,'20200602','20201019',NULL);

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

aaron.reese SSChampion Points: 13498 More actions · Answer 2

OK, you learn something new every day...

Just ran the create script for the index and it contains a WHERE condition.

CREATE UNIQUE NONCLUSTERED INDEX [IX_TableName_Current] ON [dbo].[TableName]
(
[Entity_Identifier] ASC
)
INCLUDE([AttributeField_1],[AttributeField_2]...) 
WHERE ([SnapshotCurrentFlag_BT]=(1))

So the only records in the index are the active records. Being doing this job for 30 years and didn't know you could do that! There will be only one current record for each identifier at any given time

Thom A SSC Guru Points: 99126 More actions · Answer 3

Any INDEX can have a filter on it, from a WHERE clause. Though, as you have seen, these are most commonly seen on UNIQUE INDEXs as they allow for behaviour like this, such as allowing only one row exist in the table for a specific ID that is "active". On the other hand a UNIQUE CONSTRAINT cannot be filtered, it must include all rows in the table.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk