INDEX and SELECT * query in SQL Server 2008

Thursday, February 4, 2010 |

“SELECT *” in T-SQL select query affect performance of the query adversely, there are many reasons about why we shouldn’t use “SELECT *” query but today I am going to share one of my observation about how index works with “SELECT *” query. In short, if you really don’t want ALL FIELDS in result sets, don’t use “*” as it increase IO load, network traffic. One more reason is, it is not readable that which columns you are going to use so better to use Field Name rather “*”.

There are two types of main index 1.) Clustered Index 2.) non-Clustered Index. What are these indexes and how internally it works, is something out of the scope of this article but I would like to clear one point that there are two possibilities about index when you run SELECT query. Either it will “scan” index or it will “seek” index.  Generally for big tables “SEEK” is good than “SCAN” as “SCAN” touches every row of the table. For more details on SEEK and SCAN, please read my friend and guru Pinal Dave’s article.

Actually I and my friend Bihag were discussing about Indexes, SEEK and SCAN. While our discussion, I felt to write this article.

Let us look small example to see in which situation we will get SCAN and in which situation we will get SEEK. Remember, we should try to achieve SEEK rather SCAN.

--create table for demo
create table ClusteredIndexTest
      ID INT identity(1,1),
    FirstName varchar(10),
    LastName varchar(10)

--create clustered index on our demo table

--create non clustered index on demo table
CREATE NONCLUSTERED INDEX idx2 ON ClusteredIndexTest(LastName)

--insert few records.
INSERT INTO ClusteredIndexTest
SELECT 'Ritesh','shah' union all
SELECT 'Rajan','Jain'

--now select below given query, press CTRL+M to include actual Execution Plan with query results
--and hit F5 to run query
Select * From ClusteredIndexTest --(Always Clustered Index Scan)
Select * From ClusteredIndexTest Where ID=1 --(Clustered Index Seek)
Select * From ClusteredIndexTest Where LastName='shah' --(ALWAYS CLUSTERED INDEX SCAN DUE TO SELECT *)
Select LastName from ClusteredIndexTest Where LastName='shah' --(Always Index Seek on LastName Key)

Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of


Vijaya Kadiyala said...

Hi Ritesh
Good explanation. It would be great if you post/attach execution plans also in this.

In addition to this i wrote one article on "Avoid using * in the SELECT Clause"

Ritesh Shah said...

Hi Vijaya,

That is very true but I being lazy while preparing article. :) that is why in my blog you will find very few screen snap but I think now this is the time I have to start it. thanks for the suggestions