SQL SERVER – Tricks to Replace SELECT * with Column Names – SQL in Sixty Seconds #017 – Video

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 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 *:

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)

13 thoughts on “SQL SERVER – Tricks to Replace SELECT * with Column Names – SQL in Sixty Seconds #017 – Video

  1. 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.

    Like

  2. 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.

    Like

  3. 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.

    Like

  4. I use “SELECT * ” for adhoc query in SSMS. Usually it is convenient to type “SELECT * FROM [TABLE]” to view some data.

    Like

  5. @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 *

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

  7. 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 “*”.

    Like

  8. 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,

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s