Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Back in the days of yesteryear, it was considered a big no-no to do select * from table or select count(*) from table because of the performance hit.

Is this still the case in later versions of SQL Server (I'm using 2012, but I guess the question would apply to 2008 - 2014)?

Edit: Since people seem to be slating me slightly here, I'm looking at this from a benchmark/academical point of view, not whether it's the "right" thing to do (which of course it's not)

share|improve this question
17  
I consider select * bad coding style in any DBMS not just SQL Server. –  a_horse_with_no_name Jun 30 at 14:10
6  
Why would you think that select * would be better in SQL Server 2012? –  bluefeet Jun 30 at 14:13
8  
COUNT(*) is fine though, I'm not sure it has ever not been fine... –  Mark Sinkinson Jun 30 at 14:14
2  
An objection to * even if you need all the columns now is that later someone may add extra columns. Then your application will be bringing back a load of extra columns that aren't needed. And yes that issue still exists obviously. –  Martin Smith Jun 30 at 14:40
3  
Would you bring the whole house if you just needed the fridge? –  Drew Jun 30 at 19:20

8 Answers 8

up vote 37 down vote accepted

If you SELECT COUNT(*) FROM TABLE that only returns one row (the count), is relatively light, and is the way to get that datum.

And SELECT * is not a physical no-no, in that it is legal and allowed.

However, the problem with SELECT * is that you can cause a lot more data movement. You operate on every column in the table. If your SELECT only includes a few columns, you might be able to get your answer from an index or indexes, which reduces the I/O and also the impact on the server cache.

So, Yes it is recommended against as a general practice because it is wasteful of your resources.

The only real benefit of SELECT * is not typing all the column names. But from SSMS you can use drag and drop to get the column names in your query and delete those that you do not need.

An analogy: If someone uses SELECT * when they do not need every column, would they also use SELECT without a WHERE (or some other limiting clause) when they do not need every row?

share|improve this answer
9  
It might also be worth mentioning if exists(select * from table) - which is OK since 2008 IIRC. Prior to that it was preferred to select NULL or select 1 to reduce IO. –  DaveShaw Jun 30 at 19:14
4  
ANd I will point out that if you have an inner join there is a 100% chance you are returning more data than you need. –  HLGEM Jul 1 at 13:49
8  
@Lie I don't buy it. The waste in every single case can't possibly compensate for the savings you get for the (hopefully rare!) scenario where you are changing columns. The query should probably be reviewed in that case anyway because if it is joining or filtering on columns and you've added/dropped/renamed/moved those, won't the query still have potential problems? And if you've added columns, are you absolutely certain the application will actually want to use all of those columns? Perhaps not, in which case pulling them is a total waste. –  Aaron Bertrand Jul 3 at 14:29
6  
@Lie And if "performance only matters when it matters," please let everyone know where you work. Consultants, trainers and performance monitoring tools vendors like me build their entire livelihood on such statements... –  Aaron Bertrand Jul 3 at 22:50
6  
@Lie I've been called in to deal with 3 separate performance issues as a result of misapplication of ORMs in the past month. Performance matters as soon as there is a financial implication to its absence. –  Mark Storey-Smith Jul 3 at 22:53

In addition to the answer already provider, I feel that it's worth pointing out that developers are often too lazy when working with modern ORM's such as Entity Framework. Whilst DBA's try their hardest to avoid SELECT *, developers often write the semantically equivalent eg, in c# Linq:

var someVariable = db.MyTable.Where(entity => entity.FirstName == "User").ToList();

In essence, this would result in the following:

SELECT * FROM MyTable WHERE FirstName = 'User'

There is also an additional overhead which hasn't already been covered. That is the resources required to process each column in each row to the relevant object. Furthermore, for every object kept in memory, that object must be cleaned up. If you only selected the columns that you needed, you could easily save in excess of 100mb of ram. While not a massive amount on its own, its the cumulative effect of garbage collection etc that is the cost client side.

So yes, for me at-least, it is and always will be a big no. We also need to be educating about the "hidden" costs of doing this more also.

Addendum

Here is a sample of pulling only the data you need as requested in the comments:

var someVariable = db.MyTable.Where(entity => entity.FirstName == "User")
                             .Select(entity => new { entity.FirstName, entity.LastNight });
share|improve this answer
1  
Maybe you could demonstrate the correct code to retrieve the row? –  rotard Jul 1 at 16:51
1  
@rotard I've added a sample for you :) –  Stuart Blackler Jul 1 at 20:24
3  
@RobIII It doesn't really matter if you use SELECT * or SELECT every, single, column, in, the, table - the net effect is the same (and actually worse at high scale, because now you're sending much more info over the wire every time). The point is that if you are querying columns you don't need, that's wasteful. –  Aaron Bertrand Jul 3 at 14:40
2  
@Lie And then again, the developer doesn't always know what's in the table, so may not know that SELECT * pulls a bunch of crap they don't need, accesses columns they have been denied access to, etc. While I understand why developers love SELECT *, there are a ton of reasons why DBAs are against it. Development time and maintainability are a drop in the bucket compared to application response times. Maybe you're in a shop where management cares more that you can write a query in 10 minutes instead of 11 minutes; don't assume that's true for all of us. –  Aaron Bertrand Jul 3 at 21:34
3  
At the end of the day pulling back data you don't need is wasteful. /EndOf really. –  Stuart Blackler Jul 4 at 7:49

Performance: A query with SELECT * will probably never be a covering query (Simple talk explanation, Stack Overflow explanation).

Future-proofing: Your query might return all seven columns today but if someone adds five columns over the next year then in a year your query is returning twelve columns, wasting IO and CPU.

Indexing: If you want your views and table-valued functions to participate in indexing in SQL Server then those views and functions must be created with schemabinding, which prohibits the use of SELECT *.

Best practice: never use SELECT * in production code.

For subqueries, I prefer WHERE EXISTS ( SELECT 1 FROM … ).

Edit: To address Craig Young's comment below, using "SELECT 1" in a subquery is not an "'optimisation" - it is so I can stand up in front of my class and say "don't use SELECT *, no exceptions!"

About the only exception I can think of is where the client is doing some sort of pivot-table operation and does require all the present and future columns.

I might accept an exception involving CTEs and derived tables, though I'd want to see execution plans.

Note that I consider COUNT(*) an exception to this because it is a different syntactical use of "*".

share|improve this answer
    
COUNT() should be considered as an exception, because how it works. The actual problem with the asterisk is it loads all data from all matching rows, however COUNT() is does not look up for data, just count resulting rows. Usualy, all RDBMS can optimize out COUNT() calls to count the resulting rows, because it can be easily assumed the data will not used anywhere. –  Gabor Garami Jul 3 at 11:13
    
Oops, that should have said "I do consider COUNT(*) an exception". :-( Thanks Gabor, I've corrected my post. –  Greenstone Walker Jul 4 at 4:43
    
A SELECT * can very well use a covering index. –  usr Jul 6 at 0:26
1  
@usr Do you mean in absence of a clustered index? After all, unless the table is a heap (and the index is filtered and the query matches the filter predicate), it would typically be a coin flip between the clustered index and a non-clustered index that had every single column. –  Aaron Bertrand Jul 7 at 18:40
    
@AaronBertrand example: A selective seek on a covering NCI (in the presence of a CI, doesn't matter). –  usr Jul 7 at 19:44

In SQL Server 2012, (or any version from 2005 up), using SELECT *... is only a possible performance problem in the top-level SELECT statement of a query.

So it is NOT a problem in Views(*), in subqueries, in EXIST clauses, in CTEs, nor in SELECT COUNT(*).. etc., etc. Note, that this is probably also true for Oracle, and DB2, and maybe PostGres (not sure), but it is very likely that it is still a problem in a lot of cases for MySql.

To understand why (and why it is still can be a problem in a top-level SELECT), it is helpful to understand why it ever was a problem, which is because using SELECT *.. means "return ALL of the columns". In general this will return a lot more data than you really want, which obviously can result in lots more IO, both disk and network.

What is less obvious is that this also restricts what indexes and query plans a SQL optimizer can use, because it knows that it must ultimately return all of the data columns. If it could know ahead of time that you only want certain columns, then it often can use more efficient query plans by taking advantage of indexes that only have those columns. Fortunately there is a way for it to know this ahead of time, which is for you to explicitly specify the columns you want in the column list. But when you use "*", you are forgoing this in favor of "just give me everything, I'll figure out what I need."

Yes, there is also additional CPU and memory usage to processing every column, but it is almost always minor compared to these two things: the significant extra disk and network bandwidth required for columns that you don't need, and having to use a less optimized query plan because it has to include every column.

So what changed? Basically, the SQL Optimizers sucessfully incorporated a feature called "Column Optimization" that just means, that they can now figure out in the lower-level sub-queries if you are ever going to actually use a column in the upper levels of the query.

The upshot of this is that it doesn't matter anymore if you use 'SELECT *..' in the lower/inner levels of a query. Instead, what really matters is what is in the column list of the top-level SELECT. Unless you use SELECT *.. in the top, then it once again, must assume that you want ALL of the columns, and so cannot employ column optimizations effectively.

(* -- note that there is a different, minor binding problem in Views with * where they do not always register the change in columns lists when "*" is used. There are other ways to address this and it does not affect performance.)

share|improve this answer
2  
It's still "dangerous" in indexed or materialized views where you're telling the system to store all of those columns, perhaps unnecessarily. –  Damien_The_Unbeliever Jul 1 at 13:05
    
@Damien_The_Unbeliever Agreed, good point. –  RBarryYoung Jul 2 at 18:56
    
Stating that it's a performance problem on in the top-level SELECT of a query is perhaps a slight oversimplification. For example, a CTE where each CTE SELECT chooses specific columns would have no problem if your top-level SELECT is SELECT * because you want all columns provided in the CTEs. So it might be more precise to say that the overall query should restrict columns to only those required. –  Craig Young Jul 8 at 17:20
    
As for views with SELECT *, they don't automatically register column changes (though that should be a nice and simple feature to add). This can result in not seeing new columns and errors with the view if it tries to access a column that no longer exists. Personally I prefer views that expose all available columns (except when security concerns dictate otherwise). The user of the view should in any case be selecting specific columns. So as a workaround, I always re-run ALTER VIEW if the columns change. –  Craig Young Jul 8 at 17:26
    
@CraigYoung Yes0, the View's Columns issue is the binding problem I mentioned in the footnote. –  RBarryYoung Jul 8 at 20:19

It is physically and problematically allowed to use select * from table, however, it's a bad idea. Why?

First of all, you'll find that you're returning columns that you don't need (resource heavy).

Secondly, it'll take longer on a large table than naming the columns because when you select *, you're actually selecting the column names from the database and saying "give me the data that's associated with columns that have names in this other list." While this is quick for the programmer, imagine doing this look up on a bank's computer that might have literally hundreds of thousands of lookups in a minute.

Thirdly, doing this actually makes it harder for the developer. How often do you need to flip back and forth from SSMS to VS to get all a of the column names?

Fourthly, it's a sign of lazy programming and I don't think that any developer would want that reputation.

share|improve this answer
    
Your second argument in this current form have some small mistakes. First, all RDBMS caches the scheme of the tables, mostly because the scheme will be loaded anyway at the query parsing stage for determine which column exists or missing in the table from the query. So, the query parser already queried the column name list on its own, and instantly replaces * with a list of the columns. Then, most RDBMS engines are try to cache all what it can, so if you issue SELECT * FROM table, then the compiled query will be cached so the parsing does not happen every time. And developers are lazy :-) –  Gabor Garami Jul 3 at 11:18
    
Regarding your second argument, this is a common misconception - the problem with SELECT * is not metadata lookup, since if you name the columns, SQL Server still has to validate their names, check data types, etc. –  Aaron Bertrand Jul 3 at 21:39
    
@Gabor One of the issues with SELECT * happens when you put that in a view. If you change the underlying schema, the view can become confused - it now has a different concept of the table's schema (its own) than the table itself. I talk about this here. –  Aaron Bertrand Jul 3 at 21:40

It can be a problem if you put the 'Select * ...' code in a program, because, as pointed out earlier, the database might change over time and have more columns than what you expected when you wrote the query. This can lead to program failure (best case) or the program might go on its merry way and corrupt some data because it's looking at field values that it wasn't written to handle. In short, production code should ALWAYS specify the fields to be returned in the SELECT.

Having said that, I have less problem when the 'Select *' is part of an 'IN' clause, since all that's going to be returned to the program is a boolean indicating the success or failure of the select. Others may disagree with this stand and I respect their opinion on that. It MAY be slightly less efficient to code 'Select *' than it is to code 'Select 1' in an 'IN' clause, but I don't think there's any danger of data corruption, either way.

share|improve this answer
    
Why would you use IN (SELECT *)? Did you mean EXISTS? If so, no, it doesn't matter what you put there. If you really meant IN, I don't know how that query would work (I realize IN (SELECT *) is legal syntax in the case of a single-column table, but I fail to see the purpose of IN (SELECT '1') - may as well just say 1 = 0 or 1 = 1. –  Aaron Bertrand Jul 3 at 21:37
    
Actually, yes, I had meant to reference the EXISTS clause. My mistake. –  Mark Ross Jul 5 at 6:11

There's one more small reason to not use SELECT *: if the order of the columns returned changes, your application will break... if you're lucky. If you're not, you'll have a subtle bug that could go undetected for a long time. The order of fields in a table is an implementation detail which should never be considered by applications, as the only time it is even visible is if you use a SELECT *.

share|improve this answer
1  
This is irrelevant. If you're accessing the columns by column index in your application code, then you deserve to have a broken application. Accessing the columns by name always produces much more readable application code and it is almost never the performance bottleneck. –  Lie Ryan Jul 2 at 17:58
    
@Lie now that I can agree with. –  Aaron Bertrand Jul 3 at 21:49
    
@LieRyan I understand what you're saying, and agree to a point. But people rely on column order without even realising it. See point #2 of the following answer: dba.stackexchange.com/a/69555/10426 –  Craig Young Jul 9 at 14:58

Lots of answers why select * is wrong, so I'll cover when I feel it's right or at least OK.

1) In an EXISTS, the content of the SELECT part of the query is ignored, so you can even write SELECT 1/0 and it won't error. EXISTS just verifies that some data would return and returns a boolean based on that.

IF EXISTS(
    SELECT * FROM Table WHERE X=@Y
)

2) This might start a firestorm, but I like using select * in my history table triggers. By select *, it prevents the main table from getting a new column without adding the column to the history table as well by it error'ing immediately when inserted/updated/deleted into main table. This has prevented numerous times where developers adding columns and forgot to add it to the history table.

share|improve this answer
2  
I still prefer SELECT 1 because it most obviously notifies future code maintainers of your intent. It's not a requirement, but if I see ... WHERE EXISTS (SELECT 1 ...) it pretty obviously announces itself as a truth test. –  swasheck Jul 3 at 4:07
1  
@zlatanMany people use SELECT 1 based on a myth that performance would be better than SELECT *. However, both options are perfectly acceptable. There's no differerce in performance due to the way the optimser handles EXISTS. Nor any difference in readability because of the word "EXISTS" that clearly announces a truth test. –  Craig Young Jul 9 at 15:05
    
On point #2, I understand your reasoning, but there are still risks. Let me 'paint a scenario for you'... Developer adds Column8 to the main table forgetting the history table. Developer writes a bunch of code realted to Column 8. Then he adds Column9 to the main table; this time remembering to also add to history. Later when testing he realises he forgot to add Column9 to history (thank to your error detection technique), and promptly adds it. Now the trigger seems to work, but the data in columns 8 & 9 is mixed up in the history. :S –  Craig Young Jul 9 at 15:14
    
cont... The point is that the above 'concocted' scenario is just one of many that could result in your error detection trick failing you, and actually making things worse. Basically you need a better technique. One that doesn't rely on your trigger making assumptions about the order of the columns in a table that you select from. Suggestions: - Personal code reviews with checklists of your common mistakes. - Peer code reviews. - Alternate technique for tracking history (personally I consider trigger based mechanisms to be reactive instead of proactive, and therefore prone to errors). –  Craig Young Jul 9 at 15:21
    
@CraigYoung That is a possibility. But I would throttle someone if they did that. That is not a mistake you could easily make –  Ghost Jul 9 at 15:21

protected by Mark Storey-Smith Jul 2 at 11:04

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.