You might have heard many times that one should not use SELECT * as there are many disadvantages to the usage of the SELECT *. I also believe that there are always rare occasion when we need every single column of the query. In most of the cases, we only need a few columns of the query and we should retrieve only those columns. SELECT * has many disadvantages. Let me list a few and remaining you can add as a comment.

1.       Retrieves unnecessary columns and increases network traffic

2.       When a new columns are added views needs to be refreshed manually

3.       Leads to usage of sub-optimal execution plan

4.       Uses clustered index in most of the cases instead of using optimal index

5.       It is difficult to debug.

There are two quick tricks for how users can avoid using SELECT * but instead list the column names.

1) Drag the columns folder from SQL Server Management Studio to Query Editor
2) Right Click on Table Name >> Script table as >> SELECT To… >> Select option

It is extremely easy to list the column names in the table.

  Modified On Sep-18-2014 01:24:04 PM

Leave Comment