INDEX and SELECT * query in SQL Server 2008

“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)
)
GO
–create clustered index on our demo table
CREATE CLUSTERED INDEX idx1 ON ClusteredIndexTest(ID)
GO
–create non clustered index on demo table
CREATE NONCLUSTERED INDEX idx2 ON ClusteredIndexTest(LastName)
GO
–insert few records.
INSERT INTO ClusteredIndexTest
SELECT ‘Ritesh’,‘shah’ union all
SELECT ‘Rajan’,‘Jain’
GO
–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
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “INDEX and SELECT * query in SQL Server 2008”

  1. 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

Comments are closed.