Sql Server Filtered Index as a Unique Constraint on a Specific Value

Indexes often double as constraints. Each combination of values must only appear once among the columns of an index defined as UNIQUE. The “filtered” index allows some refinement. Suppose that in one column you won’t allow a specific value to appear more than once, but you don’t mind other values being duplicated.

A good example would be the IS_LATEST_VERSION column in a data warehouse table. More than one ‘Y’ is not OK at the primary key level. The only way to make absolutely sure you don’t see duplicates is to put the constraint directly on the column. This code demonstrates how (Please DON’T try this out on big tables in your system without extensive testing):
/*
--ARE YOU SURE YOU WANT TO RUN THIS????--
DROP TABLE Equity
*/

--DO NOT USE THIS TECHNIQUE WITHOUT PROPER TESTING--
CREATE TABLE Equity
(
DatePrice date NOT NULL
,Ticker nvarchar(5) NOT NULL
,Price money NOT NULL
,IsLatestVersion char(1)
CONSTRAINT CHK_BWTIsLatestVersion CHECK(IsLatestVersion In ('Y', 'N'))
,WriteDateTime datetimeoffset
CONSTRAINT DEF_BWTWriteDateTime DEFAULT(sysdatetimeoffset())
)

--no primary key defined in this example, but index columns and ordering will follow p/key, with nullable column added--
CREATE UNIQUE INDEX IX_EquityIsLatestVersion_UQF
ON Equity(Ticker,IsLatestVersion)
WHERE IsLatestVersion = 'Y'

--add two different equities--
INSERT INTO
Equity
(
DatePrice
,Ticker
,Price
,IsLatestVersion
)
VALUES
(
CAST(GETDATE() as date)
,'ABC'
,2210
,'Y'
)
,
(
CAST(GETDATE() as date)
,'XYZ'
,2210
,'Y'
)

--add new price for ticker "ABC" making sure there is only one "latest record"--
BEGIN TRANSACTION
UPDATE Equity SET IsLatestVersion = 'N' WHERE Ticker = 'ABC' And IsLatestVersion = 'Y'
INSERT INTO
Equity
(
DatePrice
,Ticker
,Price
,IsLatestVersion
)
VALUES
(
CAST(GETDATE() as date)
,'ABC'
,2215
,'Y'
)

COMMIT

--try to add a duplicate "latest record"!--
INSERT INTO
Equity
(
DatePrice
,Ticker
,Price
,IsLatestVersion
)
VALUES
(
CAST(GETDATE() as date)
,'ABC'
,2215
,'Y'
)

--check results--
SELECT * FROM Equity

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.