This is probably the biggest problem most people have with understanding indexes.  How exactly does SQL Server decide when to use an index?  There's two big errors that people make here: assuming it'll always "just work" (it doesn't) or that they should just force it to use the indexes they think it should be using (it's nearly always slower if you do this).  Instead, it's best to understand the selection process and see if you can structure your query in such a way that it accesses the indexes you want. First things first.  It's never going to use two...

My previous article emphasized that you shouldn't mix clustered indexes and identity fields.  However, if you're using NHibernate you already probably know you shouldn't be using identity fields.  On the other hand, the points still generalize to some of the NHibernate generators: Increment: just as bad as identity HiLo:  better than identity, but not by much.  Don't mix with a clustered index. GUID:  Extremely random.  In fact, probably too random.  The inserts get plastered everywhere and can hurt performance. GUID Comb: Better than GUID, ...

We've already talked about how a level of an index looks a lot like a table.  Clustered indexes take this to the logical conclusion: they use the actual table as the leaf nodes of the index.  This means that we save a whole level of the index, which makes it faster than a non-clustered index.  (That's providing you need columns not in the index.  Otherwise index covering is typically faster.)  It also means that you've actually ordered the table according to the index. The ordering of the actual table is huge, and the ramifications are large.  The simplest, and...

Okay, first off, there hasn't been a post in, conservatively forever.  There have been a number of reasons for this: holidays, people visiting during the weekend (when I write this blog) and swine flu are probably the top three.  That and I managed to lose my post on Liskov, so I'm going to have to re-write.  But anyway, I thought that more than one person I know could do with reading a quick guide to SQL Server indexes. Basics of SQL Server Performance The first thing you need to know about is pages.  SQL Server puts rows onto...