Big Data, Data Analytics and Data Science | BI-BIGDATA.com

People always keep saying do not use SELECT * exercise SELECT <column names> instead. But no one put more light on this and this becoming myth in SQL Server and other database management systems. Here are couple of reasons why should we avoid SELECT * and go with SELECT <column names>

• In SELECT * we are not providing any column hints to SQL Engine as which columns we are looking for, in background SQL Engine will check the table’s metadata to generate output columns list. This additional lookup is not mentioned in actual execution plan but will add additional overheads to query if not cached.’

SELECT * Execution Plan

SELECT <column names> Execution Plan

• The data would be retrieve from the index pages if SQL query contains all the necessary columns and thus disk I/O and memory overhead would be much less compared to doing a SELECT *.
• In SELECT <column names> query, there’s always a possibility of a non-clustered index might be serving SQL Engine’s request. While we do not have that possibility in SELECT *.
• If someone changed existing metadata of table object and winded up with adding image/text data type columns  will be additional overhead to all SELECT * operations.

Hope this helps to you guys.

Thanks & Regards,

Sandip Shinde