code_junkie

How to create a unique index on a NULL column?

Nov 11th, 2011
86
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.34 KB | None | 0 0
  1. CREATE VIEW vw_unq WITH SCHEMABINDING AS
  2. SELECT Column1
  3. FROM MyTable
  4. WHERE Column1 IS NOT NULL
  5.  
  6. CREATE UNIQUE CLUSTERED INDEX unq_idx ON vw_unq (Column1)
  7.  
  8. CREATE TABLE dupNulls (
  9. pk int identity(1,1) primary key,
  10. X int NULL,
  11. nullbuster as (case when X is null then pk else 0 end),
  12. CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
  13. )
Add Comment
Please, Sign In to add comment