“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
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),
–create clustered index on our demo table
CREATE CLUSTERED INDEX idx1 ON ClusteredIndexTest(ID)
–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
–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)