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
blog.philipsymons.com 