"SELECT *" why is it an antipattern

Question

On multiple questions here and on stack overflow i saw people saying in the comments and answers that select * from table is almost always an antipattern without any explaination why. Althrough I can sort of deduce why it is an antipattern. I may be looking over a detail that someone else with better understanding of the issue noticed.

So here is my question, why people say that select * is an antipattern.

Answer 1

The two reasons that I find the most compelling not to use SELECT * in SQL Server are

1. Memory Grants
2. Index usage

Memory Grants

When queries need to Sort, Hash, or go Parallel, they ask for memory for those operations. The size of the memory grant is based on the size of the data, both row and column wise.

String data especially has an impact on this, since the optimizer guesses half of the defined length as the 'fullness' of the column. So for a VARCHAR 100, it's 50 bytes * the number of rows.

Using Stack Overflow as an example, if I run these queries against the Users table:

SELECT TOP 1000 DisplayName
FROM dbo.Users AS u
ORDER BY u.Reputation;

SELECT TOP 1000 DisplayName, u.Location
FROM dbo.Users AS u
ORDER BY u.Reputation;

DisplayName is NVARCHAR 40, and Location is NVARCHAR 100.

Without an index on Reputation, SQL Server needs to sort the data on its own.

But the memory it nearly doubles.

DisplayName:

DisplayName, Location:

This gets much worse with SELECT *, asking for 8.2 GB of memory:

It does this to cope with the larger amount of data it needs to pass through the Sort operator, including the AboutMe column, which has a MAX length.

Index Usage

If I have this index on the Users table:

CREATE NONCLUSTERED INDEX ix_Users
    ON dbo.Users
(
    CreationDate ASC,
    Reputation ASC,
    Id ASC 
 );

And I have this query, with a WHERE clause that matches the index, but doesn't cover/include all the columns the query is selecting...

SELECT u.*, 
       p.Id AS [PostId]
FROM   dbo.Users AS u
JOIN   dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE  u.CreationDate > '20171001'
       AND u.Reputation > 100
       AND p.PostTypeId = 1
ORDER BY u.Id

The optimizer may choose not to use the narrow index with a key lookup, in favor of just scanning the clustered index.

You would either have to create a very wide index, or experiment with rewrites to get the narrow index chosen, even though using the narrow index results in a much faster query.

CX:

 SQL Server Execution Times:
   CPU time = 6374 ms,  elapsed time = 4165 ms.

NC:

 SQL Server Execution Times:
   CPU time = 1623 ms,  elapsed time = 875 ms.

Answer 2

It is a question of control.

Using 'select * from...' leaves the number of returned columns and ordering of the returned columns undefined.

Many programmatic interfaces to the database depend on the number and order of returned values.

Answer 3

Two main reasons:

Firstly, your database has to go and work out which columns are available on the table[s] that you're querying. That's an additional overhead and, in [older versions of] older DBMSs, that could be a [very] significant performance bottleneck if there was lots of contention around the System Catalog tables holding this information.

Secondly, it assumes that you know everything about these tables and that you always will. Database are inherently shared resources and will, over time, have many people working on them, changing the data structures to keep up with changing requirements. Today, you could create a table with two columns (say, "username" and "password_hash") and use that as the core of your Application's login screen. Everything works well for ages ... until someone [else] comes along and shoehorns into your tidy, little table a couple of dozen extra, "blob" fields, containing gigantic data or photos or whatever. Suddenly, your login screen slows to a crawl because the query is pulling back all ("*") those extra, gigantic, network traffic-bloating fields in which it has no interest at all!

Also, you can write code that depends on the order in which fields are returned (you shouldn't, but you can). This is most common in reporting circles, particularly if data is "handed off" to a User's spreadsheet program of choice. When the table structure changes, fields can "move around" and your (their?) data "suddenly" starts arriving in the "wrong" order.

Always ask for the columns that you need, in the order in which you need them.

And, just for completeness, remember the "order by " clause - rows in [Relational] tables have no intrinsic order.