Production Perfomance of using SELECT * statements

问题:

Why is the use of SELECT * in production code considered bad practice? Is there any alternative for using in production, or do we need to SELECT every column in the table individually?

回答1:

Using SELECT * is a bad practice for a few reasons, at least in the context of SSIS.

Selecting all the columns, if you don't need all of them, is a waste of memory and memory is the magic that makes SSIS run fast (or not).

Selecting all the columns, if you don't need all of them, will prevent SQL Server from using an index to return your data faster to you.

SSIS is very meta-data driven. Therefore, if you have SELECT * as your source, if a column is added to the table your source definition has changed and your package may fail as the metadata is no longer consistent.

回答2:

[*] is effectively selecting all columns. But since selecting all columns means getting a lot of data, i would say that selecting just the columns you really need is better.

For instance you may not need to fetch all id columns or timestamps or update dates etc. But you would want to fetch product descriptions, or customer names. But even then you may not need to fetch all each time.

回答3:

If you truly need every column in the database call that you are making then there shouldn't be a performance impact.

If you are frequently doing SELECT * ... and only using a one or two columns, then this is the location to refactor your SELECT statements as it will return all columns when you are only interested in one or two.

Selecting column names also makes it easier to read and modify existing SQL in my opinion.