STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

Extremely easy tricks to list table columns’ names and replace SELECT *

Everyone knows the simple T-SQL “SELECT * ” is bad practice. It can:

  1. Effects the execution plan (Table / Index Scan);

  2. Returns too much data: unnecessary extra reads, memory grant, increases network traffic;

  3. Confusing possibilities: retrieving of columns with the same name from two different tables;

  4. Columns definition changes in the source table, requires manually adding a view for the definition refresh;

… and much more.

So, how can we simply replace a star “ * ” with table column names in a few seconds?

There are a few tricks that are extremely easy to list the column names from the table.

Trick 1:

Find the table, expand it and easily drag “columns” folder to the Query Editor.

Now we can remove “ * ” and any unnecessary columns’ names.

Trick 2:

Find the table, right click on table name, after that choose Script Table as > SELECT To > New Query Editor Window

Now we can remove unwanted columns and keep the necessary columns.

Trick 3:

Select a statement and press Ctrl+Shift+Q

or SSMS > Main menu > Query > Design Query in Editor to open Query Designer

Copy column list from the bottom window of the Designer.

Once again, keep the necessary columns and remove unwanted columns.

It is really cool and easy, right? 🙂

#replace #easiest #trick #bad #star #sqlserver #select #columns #tsql #tip #howto #performance #practice

JOIN OUR MAILING LIST

CONTACT US

4 Itzhak Ben Zvi, Hod-Ha'sharon,

Israel 4537302

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle