Which SQL indexes are faster?
Even wondered what sort of index lookups are the best for your queries? What is the preferred speed order. Well, of course, it depends. In some cases there is little difference and it usually depends on whether the query optimiser thinks it will only need a subset of data from an index and also how much data is in the index compared to the main table. Bear in mind that just because you might get an Index Seek/Scan, it doesn't mean that's the fastest you can go. Using a large index for an index seek might be 10s of times slower than creating a smaller index with the required columns. Anyway, in order fastest to slowest, this is the best I can come up with:
Index Seek - A subset of an index. It's speed relates to how few columns you can include to get what you need and you will need some sort of 'where' clause for the optimiser to reduce rows as it joins. Make sure you include your WHERE clause columns and any JOIN columns in the 'index columns' you can then include other columns you want to select in the 'additional columns' area.
Index Scan - this scans the whole index and might be fast if it contains few columns and/or rows otherwise it is little better than a clustered index scan or table scan. If you have indexes that contain most columns from the underlying table, you are probably better off removing the index or redesigning your queries to not require so many columns. Also, ensure that you include where clauses as early as possible in your queries (i.e. not all at the outer level) which might promote an Index Scan to an Index Seek.
Key/RID Lookup - this might be very slow since it requires an index seek/scan followed by an index/clustered index/table lookup in which case it is not much better than a clustered index scan. It might otherwise be somewhere closer to the speed of an Index Scan if it comes from an Index Seek for a small subset of data. Generally best avoided by modifying/creating other indexes.
Clustered Index Scan - this is usually not much faster than a table scan, if at all, since the only difference is the clustered index is ordered by something which may or may not be useful for a particular query to use. The clustered index itself is however very helpful to speed up joins in general so don't delete the clustered index just because it isn't much better than a table scan. In the case of a small table with few columns/rows this index is probably as fast as it will get since more specific indexes will not be significantly smaller than the underlying table/clustered index. In this case, do not seek to replace Clustered Index Scans with Index Scans.
Table Scan - the base table is scanned, every row. The slowest and best avoided since it not only affects performance by itself but can also slow joins to other tables/views. It increases the amount of IO required even if you don't need all of the columns returned. This is because the data is paged into memory as it is being queried. A smaller set of data in an index takes up less memory and therefore less pages = faster.
I have also seen Clustered Index Seek and Table Seek. These are better than their Scan cousins but still best avoided if possible using an index. The only time clustered or table seeks might be the fastest you would get, like clustered and table scans, is if the underlying table is small or basic.
Index Seek - A subset of an index. It's speed relates to how few columns you can include to get what you need and you will need some sort of 'where' clause for the optimiser to reduce rows as it joins. Make sure you include your WHERE clause columns and any JOIN columns in the 'index columns' you can then include other columns you want to select in the 'additional columns' area.
Index Scan - this scans the whole index and might be fast if it contains few columns and/or rows otherwise it is little better than a clustered index scan or table scan. If you have indexes that contain most columns from the underlying table, you are probably better off removing the index or redesigning your queries to not require so many columns. Also, ensure that you include where clauses as early as possible in your queries (i.e. not all at the outer level) which might promote an Index Scan to an Index Seek.
Key/RID Lookup - this might be very slow since it requires an index seek/scan followed by an index/clustered index/table lookup in which case it is not much better than a clustered index scan. It might otherwise be somewhere closer to the speed of an Index Scan if it comes from an Index Seek for a small subset of data. Generally best avoided by modifying/creating other indexes.
Clustered Index Scan - this is usually not much faster than a table scan, if at all, since the only difference is the clustered index is ordered by something which may or may not be useful for a particular query to use. The clustered index itself is however very helpful to speed up joins in general so don't delete the clustered index just because it isn't much better than a table scan. In the case of a small table with few columns/rows this index is probably as fast as it will get since more specific indexes will not be significantly smaller than the underlying table/clustered index. In this case, do not seek to replace Clustered Index Scans with Index Scans.
Table Scan - the base table is scanned, every row. The slowest and best avoided since it not only affects performance by itself but can also slow joins to other tables/views. It increases the amount of IO required even if you don't need all of the columns returned. This is because the data is paged into memory as it is being queried. A smaller set of data in an index takes up less memory and therefore less pages = faster.
I have also seen Clustered Index Seek and Table Seek. These are better than their Scan cousins but still best avoided if possible using an index. The only time clustered or table seeks might be the fastest you would get, like clustered and table scans, is if the underlying table is small or basic.