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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 47 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

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

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

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

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
2  
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 '14 at 17:58

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 '14 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 '14 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 '14 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 EXISTS 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 EXISTS clause, but I don't think there's any danger of data corruption, either way.

share|improve this answer
    
Actually, yes, I had meant to reference the EXISTS clause. My mistake. – Mark Ross Jul 5 '14 at 6:11

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
3  
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 '14 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 '14 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 '14 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 '14 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 '14 at 15:21

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

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, 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.