Wednesday, May 2, 2012

What is Best for Table Scan? Clustered Index or No Index

Always we believe Clustered Index is the best. However, what it will for table scan.

Here is the simple test of it.

First let me create a data to play around.

image

Then let me create a table with clustered primary key.

image

Then populate some data. Following script will some time to populate considering the page splits.

image

Then let me create a table with no index.

image

As before let me populate same set of data to the table with no indexes.

image

Now let us select data from both tables.

image

Following times shows that table with index has taken more time to read than table with no indexes.

image

and execution plan confirmed it.

image

Question is why?

Let us see the fragmentation on tables.

image

Result is,

image

So this tells you the story. Since you have clustered index, there are fragmentations. In the above scenario, table with clustered index has 26,330 pages while table with no index has 18,519 which is why table with no index is faster.

Ok, let us do rebuild the index and rerun all the queries with clearing the cache. Executions plans are 50/50.

image

Let us see number of pages for both cases.

image

In this you can see pages are high in table without any indexes. However, you will see that querying the entire table is slightly higher in the table with clustered index.

Here is the sample script you can play around.

No comments:

Post a Comment