>> ... what's the practical difference? Meaning, when do you need one or the other?


Like most things in the computer industry, it depends <s>.

In general, the clustered index is the most useful. It is very good for solving both range and point queries. A range query usually returns a large number of row, sometimes in sequence. A query that contained a BETWEEN x AND y would be considered a range query. A point query returns a small number of rows. A search on the PK would be considered a point query.

If you are constantly searching for a range of rows (ie, WHERE orddate BETWEEN @start AND @end) you'll get very good performance if you have a CL index on the orddate column. SQL Server will have to find the first row that fits the criteria and then scan the table forward until it finds the last, or the end of the table. Or, if you are constantly sorting on orddate, SQL Server may not need to do the sort since the data is physically in the correct order already.

NC indexes are horrible for range queries. The leaf level of the index has the keys in the correct order but SQL Server will have to make an extra jump from the leaf of the index to the table.

It's easy to see the problem: Create a table with a decent number of rows, say more than 50,000. Build a NC index on one of the columns. Run the following batch:

SET STATISTICS IO ON

-- Return a small result set
SELECT * FROM table WHERE column < small_ value

-- Return a large result set. Hopefully SQL Server will
-- decide to do a table scan
SELECT * FROM table WHERE column < large_value

-- Return a large result set and force SQL Server to use the NC index
SELECT * FROM table (nc_index_name) WHERE column < large_value

It's important to do the SELECT * to force SQL Server to read the table.

-Mike