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 (https://blog.sqlauthority.com)
18 Comments. Leave new
Good tips but still not nearly as easy as using SQL prompt. Love you stuff!
Nice Post
i prefer developers do not use SELECT * for the reasons you cited. i was wondering if there are properties you can apply on a view that make SELECT * return an error. i might be thinking of mysql.
One more way is to select a statement and press Ctrl+Shift+Q to open query design editor and click OK on it..
Out of habit, when using osql/sqlcmd I usually grab the column list by hand using:
select ‘,’+c.name — edit out the first , on row 1 of course
from syscolumns c
,sysobjects o
where c.id = o.id
and o.type in (‘u’, ‘v’) — Or what have you.
and o.name = ‘object name’
GO
Then just copy/paste the results into my query/view/proc etc.
Though in Management Studio I will try Chintak’s solution I think as it can be down with minimal movement away from the text editor.
Thanks, I wasn’t aware of reason #2. I did a few quick tests to confirm it’s correct. Interestingly, #2 doesn’t apply to select * in a stored proc, although other reasons still apply.
Also, I didn’t know you could drag & drop the list of column names in SSMS. I played around and realized that there are many DB objects and collections of objects that you can drag & drop including table, view, proc and function names and collections such as column or parameter names, as well as individual column or parameter names. Where applicable, (e.g. tables, views, procs and functions), it includes the schema and wraps everything in square brackets (i.e. [dbo].[Person] ). It does not however wrap column names in square brackets in the same way.
You can also drag & drop more obscure DB objects and collections such as indexes, keys and constraints. I did notice one little bug though. When I drag the Keys collection to a query window, I get a comma separated list of the key names, but there is no comma between the primary key and any foreign keys. Anyway, thanks for educating me on reason #2 and on the drag & drop features of SSMS.
David Thank you so much for kind comment!
Select * from table ;fine tune above query .
How to fine tune it
I use “SELECT * ” for adhoc query in SSMS. Usually it is convenient to type “SELECT * FROM [TABLE]” to view some data.
@robertmcbean: You can add a dummy column in every table/view and make then set the permissions to that column as DENY SELECT. So any user belonging to this role won’t be able to write a query SELECT *
Hi, I use SELECT * when querying tables from a linked server
The whole post relies on a huge assumption which is the user has full knowledge of the table columns and his own permissions on those.
Given that “any” user may execute a predefined query (using his own credentials against the database) there is no prior knowledge of which columns are SELECT-able or not. Hence, the user SHOULD be able to perform a “SELECT *” statement and this SHOULD NOT fail because he can not see a subset of the columns or rows.
Instead, SqlServer SHOULD interpret the “*” not as “all columns” but rather “all available columns” !
This justifies the usage of “SELECT *” and this also shows that SqlServer is not executing as expected in its interpretation of the “*”.
Pinal – Thanks for the video. Is there any way to remove square bracket surrounding the column names by default? when I drag the columns they are like [col1], [col2]… When you have 20 some columns in a select query, it makes it harder to read.
same thing is true when dragging table it adds [dbo].[table]…
Thanks,
What if i want to select all columns where the column name not like ‘%SID’
Amy,
Unfortunately, you need to write custom queries. nothing in-built in SSMS.
Having the square brackets is not a bad thing, those let the optimizer know the string in the brackets is a name, not a value, so it has less work to do, much like telling it column names instead of SELECT * so the optimizer doesn’t have to work to look up the column names itself.
My opinion is that a well-written query always uses square brackets, schema names and no asterisk, such as:
SELECT [colA] AS [A], [colB] AS [B] FROM [dbo].[myTable] AS [X] WHERE [X].[A] = 1;
Granted, once a query plan is created, the query syntax is no longer important, however, I consider it to be best practice, plus, you become more familiar with the layout so it’s easier to troubleshoot down the line.
I agree with you Bart.
One reason to use a Select * is when you’re using a TVF or Stored Proc or View to view a table, so you don’t have to assign permissions directly to a table. With SELECT *, you don’t have to update the TVF when you add a column or remove one.