Previously, I covered the basics of creating an index using CREATE INDEX.  Today, I’m going to dig a little deeper.  I’d like to teach you the difference in an CLUSTERED versus a NONCLUSTERED index.  If you learn nothing else, know that a CLUSTERED INDEX physically orders the table, a NONCLUSTERED INDEX creates another “table” and orders that, each row of that “table” will point back to a record in the table you’re indexing.

CLUSTERED INDEX

CREATE CLUSTERED INDEX cix_tableName__columnName
ON tableName (columnName)

Like I was explaining before a CLUSTERED INDEX orders the corresponding rows in the table in the logical order of the index.  In my previous T-SQL statement tableName will be physically ordered by columnName.  This is like permanently applying an ORDER BY statement on this table.  Another, Microsoft, way of saying this is the bottom, or leaf, level of the clustered index contains the actual data rows of the table.

Please note: a TABLE or VIEW can only have one CLUSTERED INDEX at a time.  Think about it.  If you were going to index a table of contacts, you can’t sort by the first name and the last name at the same time.  One ordering would undo the other!  If you want to order by one column and then the other column you can use multiple columns in your CREATE INDEX statement.

CREATE CLUSTERED INDEX cix_contact__lastname_firstName
ON contact (lastname, firstName)

This would order everything like a phone book.  by last name and then first name.  For more information, see Clustered Index Structures.  Oh, and before I forget, NONCLUSTERED is the default type of indexed created.  If you don’t explicity call for a CLUSTERED INDEX, you’re not getting one.  The DBA in me thinks there ought to be another rule… always be explicit in your T-SQL, you slackers! (I know slackers isn’t an explicit word, but you get the point, right?)

NONCLUSTERED INDEX

CREATE [NONCLUSTERED] INDEX ix_tableName__columnName
ON tableName (columnName)

Again, technically the NONCLUSTERED keyword is not required by the parser, but it’s required by me.  By running this statement you are Creating an index that specifies the logical ordering of a table.  Your index is physically independent of the table you are indexing.  In this case, Microsoft might say the leaf level of the NONCLUSTERED INDEX contains a pointer to the row it refers to.  As I mentioned before, if you don’t explicitly call for a NONCLUSTERED INDEX, you get one anyway.

For more information, see Nonclustered Index Structures.

That’s the difference between these two types of indexes.  Don’t worry, I’ll be building on this topic too.  It gets more complicated from here!

If you have any questions, send them in… I’m here to help!