Ever wanted to have have a table that contains unique values but needs to have multiple null values also?
Here's how to do it:
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE TABLE t1 (id INT, title VARCHAR(20))
GO
-- optional instead of trigger to disable the insert directly into the table
CREATE TRIGGER trg_t1_DisableInsert ON t1
INSTEAD OF INSERT
AS
BEGIN
-- use 18 to stop further processing
RAISERROR (40000, 18, 1, 'Use view dbo.v1 to insert data into table')
END
GO
CREATE VIEW v1
WITH SCHEMABINDING
AS
SELECT id, title
FROM dbo.t1
WHERE id IS NOT NULL
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX CI_V1_ID
ON dbo.v1 (id);
GO
INSERT INTO v1 (id, title)
SELECT 1, 'title 1' UNION ALL
SELECT 2, 'title 2' UNION ALL
SELECT 3, 'title 3' UNION ALL
SELECT 4, 'title 4' UNION ALL
SELECT null, 'title null'
-- this fails because it clashes with the clustered index on the view
INSERT INTO v1 (id, title)
SELECT 1, 'title 5'
-- this is ok because it doesn't clash.
INSERT INTO v1 (id, title)
SELECT NULL, 'title 5'
-- we see that the table can contain only unique values and more than one null
SELECT * FROM t1
SELECT * FROM v1
GO
DROP VIEW dbo.v1
GO
DROP TABLE dbo.t1
I believe that the code is self explanatory.
This came up in a presentation on the local MS Event called NT Conference (the link is in Slovene).
