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