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

Posted in SQL Tips, Tips

Leave a Reply

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

*