I saw this question in in Google Analytics from a Google search that hit our site.

So, how can you determice if an index is clustered or not? There are two ways, you can use either the INDEXPROPERTY function or the sysindexes/sys.sysindexes system table/view

To see how it works we will create a table with one clustered and one non clustered index on it

Here is the code for that

How can you find out if the columns that are part of the index are sorted descending or ascending in that index? For example when you create the following index

We all know that we should avoid index and table scans like the plague and try to get an index seek all the time.

Okay, what will happen if we fill a table with one million rows that have the same exact value and then create a clustered index on it.

Yes, you can create a clustered index on a non unique column because SQL Server will create a uniquefier on that column to make the index unique.

Create the following table with a clustered index

A lof of time your index will get fragmented over time if you do a lot of updates or inserts and deletes.

We will look at an example by creating a table, fragmenting the heck out of it and then doing a reorganize and rebuild on the index.

First create this table

Take a look at this query.