add tag
thomas e (imported from SE)
On multiple questions I see people saying in the comments and answers that `select * from table` is almost always an antipattern, without any explaination why.

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

Why do people say that `select *` is an antipattern?
Top Answer
meme (imported from SE)
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, 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:

        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][7] to get the narrow index chosen, even though using the narrow index results in a much faster query.



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


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


This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.