0

Strange, I have been repeatedly told by everyone in every job that I worked on that always use column names rather than *. I do not talk about instances where we need only few columns, but when we need all columns.

But when I ran the sample query like below:

SELECT 
    EmpID
    ,Name
FROM [CCC].[dbo].[EmpDOType]

SELECT * from EmpDOType

I saw that * is using less time in the readings from Client Statistics

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Jasmine
  • 5,186
  • 16
  • 62
  • 114
  • 1
    possible duplicate http://stackoverflow.com/questions/3180375/select-vs-select-column – Jun Rikson Jan 22 '15 at 05:14
  • I think * is faster because it will take all columns and read row by row.. And in case of separate columns it should check each column for each row and then it should display.... so access time also high – koushik veldanda Jan 22 '15 at 05:14
  • @ColourDalnet: Thank you, will have a look in that thread too :) But possible views uncaptured in that thread are welcome here :) – Jasmine Jan 22 '15 at 05:14
  • @ColourDalnet: Well I have quickly read that post, but that thread talks about scenarios where one intend only few columns, while my question is all about needing all the columns from the table – Jasmine Jan 22 '15 at 05:18
  • It's not me mate... @Deevinee is asking it. – Jun Rikson Jan 22 '15 at 06:59
  • EDIT: NAME hey @Deevinee you should not use select * in production(as ans by @nadeem_MK) but its completely ok to use in testing or viewing the data somebody ask the same question here stackoverflow.com/questions/5565994/sql-select-clause-tuning – Neeraj Prasad Sharma Jan 22 '15 at 08:25
  • @NeerajPrasadSharma: Thank you; but say we have 100s of column, will you write every column name in query when you want some live data extract for some reporting; or you just use *? Its not easy to write all column names – Jasmine Jan 26 '15 at 23:13
  • 1
    @Deevinee IF any table has 100 columns than i will request DBA to normalize it, if DBA says no for any reason, and i need all columns for reporting, than i`ll do this method (http://sometechcompany.com/TechBlog/QuickTipDragnDropColumnNamesinSQLServerM.aspx)to avoid writing all column name. PS: I myself use a lot of " * " but never in production. – Neeraj Prasad Sharma Jan 28 '15 at 07:04

1 Answers1

2

Try SET STATISTICS TIME ON to get a more accurate results.

You will find out that doing SELECT * is actually much slower than doing a column select when measured correctly. Moreover, even though there is a slight performance difference, it is much better to avoid SELECT *, because in case a new column is added to the table in the future, you can ensure it will not cause any bug.

You can get some more arguments on this LINK

Community
  • 1
  • 1
Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
  • Nadeem, I do not understand, select * is better when you have 100s of columns. Also, when we add a new column to the table, there are ample chances that we forget to include this column in the query to retrive data, but select * would avoid this bug; but your statement is totally opposite on this view; I am afraid if I misunderstood it for something else you are trying to say? Please explain – Jasmine Jan 26 '15 at 23:12
  • Actually what I am trying to say, if ever you are doing a `select *` to retrieve data into a dataset which is bound to a grid for example. It will work ok. but tomorrow if you add a column to the table, you will have to comeback and review your grid, which might raise an error. But if you specified the columns needed (even if all the column present at the time of coding), even if in the future, more columns are added, it will not affect your grid for example. – Nadeem_MK Jan 27 '15 at 05:40
  • Nadeem, thank you, got your point and also I have myself verified about the views getting impacted on using * in the query – Jasmine Jan 29 '15 at 00:44