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.
Retrieves unnecessary columns and increases network traffic
When a new columns are added views needs to be refreshed manually
Leads to usage of sub-optimal execution plan
Uses clustered index in most of the cases instead of using optimal index
It is difficult to debug.
There are two quick tricks I have discussed in the video which explains 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. In today’s sixty seconds video, you will notice that I was able to demonstrate both the methods very quickly. From now onwards there should be no excuse for not listing ColumnName. Let me ask a question back – is there ever a reason to SELECT *? If yes, would you please share that as a comment.
More on SELECT *:
SQL SERVER – Solution – Puzzle – SELECT * vs SELECT COUNT(*)
SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*)
SQL SERVER – SELECT vs. SET Performance Comparison
I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.
If we like your idea we promise to share with you educational material.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video
