Why we should avoid * in SELECT Clause to improve query performance

Why SQL Server experts say don’t use * in SELECT clause. Always use the columns what you need. In this article I am going to list out the points with examples on why we should avoid * in the SELECT clause.

Let’s create a table with 100K records. In my case I created VJ_TEST table based sys.sysmessages using SELECT statement. To test the performance of the query you really need a big table.

Step 1: Creating a test table
select * into VJ_TEST from sys.sysmessages

From this table, I need Error,Description columns to my application based on Severity column. i.e. When ever I query this table I always use severity column.

So I created a Covering index on these columns.

Step 2: Creating Covering Index

CREATE NONCLUSTERED INDEX IDX_VJ_TEST ON VJ_TEST(SEVERITY) INCLUDE (Error,description)


Step 3A: Query the table using * and see the execution plan.
SELECT * from VJ_TEST WHERE SEVERITY = 24

Estimated CPU Cost is 0.0004598
Estimated SubTree Cost is 0.356421

Though this query is using Index to filter the records but it is still scaning the table to get all the columns.

Step 3B: Query the table using Columns and see the execution plan.
SELECT Error,description from VJ_TEST WHERE SEVERITY = 24

Estimated CPU Cost is 0.000278
Estimated SubTree Cost is 0.0048638

As you can see by specifying the columns in the where clause it is going for Index Seek.

So based on this excersise we can say using columns in where clause always faster than using *.

Point 1: Always use the required Columns in the SELECT clause
Point 2: If you are transfering less data over the network then it will ocupy less bandwidth and faster to send.
Point 3: From look and feel point of view it would be very good and easy to understand.