SQL 102 – UNIQUE INDEX
Ok, you understand how to create an INDEX. You understand the differences in CLUSTERED and NONCLUSTERED indexes. Did you know you could index a view?
You can. But before you start adding indexes, just like you would for a table, you’ll have to create an UNIQUE CLUSTERED INDEX on that index. The reason for that is simple. When you create that UNIQUE CLUSTERED INDEX, the view is physically materialized. It’s actually written and stored to disk on the database server. Once that’s complete, it’s just like a table… at least from an INDEX point of view. For more information, see Designing Indexed Views.
Now, you may be wondering what exactly is a UNIQUE INDEX?
Well it’s an index in which no two rows are allowed to have the index value. It’s like a primary key or a UNIQUE CONSTRAINT.
The Microsoft SQL Database Engine won’t allow you to create a unique index on columns that already include duplicate values. Even if you use SET IGNORE_DUP_KEY ON. If you try, you’ll get an error message. Duplicate values must be removed before a unique index can be created on the column or columns. Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created.
Can you think of a good use for a UNIQUE INDEX, other than setting up an indexed view? Let me know. I’d love to discuss this with you further.
And now you know about UNIQUE indexes, and knowing is half the battle!