SQL select * vs. select all columns
As a developer I always was confused should I write select * to bring all the data form SQL table or write select col1, col2, col2….coln the few questions came to my mind
* Which one is better in terms of performance ?
* Which one is better in terms of memory usage?
* Which one is better in terms of I/O operations and network?
In this article I’ll try to answer this questions with more details and I’ll share best practices from many sources.
select * may be used during developing, debugging and testing however there exist several reasons to not use select * for example:
1- you may return unnecessary data that will be just ignored usually we don’t need all the returned columns.
2- When you use SELECT * in a join, you can introduce complications when multiple tables have columns with the same name
3- While applications should *not* be relying on ordinal position of columns in the result set, using SELECT * will ensure that, when you add columns or change column order in the table, the shape of the resultset will change. Ideally, this should only happen intentionally and not as a surprise, Well programmed applications should be referring to columns by name and shouldn’t be affected, but well programmed applications should also minimize the ways in which they are vulnerable to external changes.
4- Often, the bigger problem with SELECT * is the effect it will have on the execution plan. While SQL Server primarily uses indexes to look up your data, if the index contains all the columns you’re requesting it doesn’t even need to look in the table. That concept is known as index coverage.
SELECT * FROM Production.TransactionHistoryArchive
WHERE ReferenceOrderID < 100
SELECT ReferenceOrderLineID FROM Production.TransactionHistoryArchive
WHERE ReferenceOrderID < 100In the above example, the first query results in a Clustered Index Scan, whereas the second query uses a much more efficient Index Seek. In this case, the Index seek is one hundred times more efficient than the Clustered Index Scan.
Unless you’ve indexed every single column in a table (which is almost never a good idea), a SELECT * query can’t take advantage of index coverage, and you’re likely to get (extremely inefficient) scan operations.
Please read the articles from resources section it a valuable and contains much more information.
