I've spent some time today reviewing the query plans for my current project, and as ever I keep forgetting what I'm trying to get to.  I found this useful page which reminded me of the orders - from best to worst:

  1. CLUSTERED INDEX SEEK - Very fast and efficient - the table is physically ordered according to specified column(s) and SQL can go find it AND since all of the data is phsically ordered right, can pull the data sequentially
  2. INDEX SEEK - which is very fast and efficient.  The SQL server knows pretty much where the data is and can go directly to it and seek out the rows it needs.  The data isn't ordered in the DB by the fields in the index so it's likely not pulling the data sequentially like it is in the CLUSTERED INDEX SEEK, but it can still translate to a massive improvement in query execution cost/speed.
  3. INDEX SCAN - which as you can see still implies using the index, but it doesn't know exactly where the data is, so it may scan the whole index or a partial range of the index to find it's data. Can be very slow and costly, but still scans through the index as opposed to scanning the physical table. 
  4. CLUSTERED INDEX SCAN - a clustered index is really not much more than a table with the data physically ordered by specified columns.  So if you see a CLUSTERED INDEX SCAN, it's pretty much the same as a TABLE SCAN for performance because SQL has to physically search through every single row in the clustered index (which really IS the table).  
  5. TABLE SCAN - slow, inefficient.  SQL has to physically look at every single row in the table. Shouldn't see this often, if you do, you may need to rewrite something or add indexes.

I've managed to remove all the scans, and everything is now at least using a seek.