Covering Indexes in SQL 2005 – Results

After a few queries regarding my recent post on Covering Indexes, I decided to do a couple of tests to show you the results.

Below are the actual execution plan results using the table structure and SQL query from the earlier post. The table had three rows of data.

Results WITHOUT a covering index
Results WITHOUT a covering index
Results WITH a covering index
Results WITH a covering index

As you can (hopefully) see, the covering index resulted in a simpler execution plan.

If we compare the two subtree costs of 0.0032831 (Covering index) and 0.0065704 (No covering index) we get a performance gain of 2.001 or almost bang on 200% improvement.

I hope this helps

You May Also Like

Leave a Reply

Your email address will not be published. Required fields are marked *