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!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *