Is select * still a big no-no on SQL Server 2012?

Question

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)

Answer 1

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?

Answer 2

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 });

Answer 3

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 "*".

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

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.