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.

Then let me create a table with clustered primary key.

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

Then let me create a table with no index.

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

Now let us select data from both tables.

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

and execution plan confirmed it.

Question is why?

Let us see the fragmentation on tables.

Result is,

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.

Let us see number of pages for both cases.

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.