Use of SELECT * :

On first encounter with SQL we always praise the genius who invented the syntax SELECT *! It's so handy and easy to use! Instead of explicitly listing all column names in our query, we just use the magic wildchar '*' and retrieve all columns. For example, a common misuse of SELECT * is to extract a set of all plastic products and to insert them into another table with the same structure:

INSERT INTO PlasticProducts
SELECT *
FROM Products
WHERE material_type = 'plastic';

Job done! However, one day business requirements change and two new columns are added to the Products table:

ALTER TABLE Products
ADD effective_start_date DATETIME,
effective_end_date DATETIME;

All of sudden the magic query results in error:

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

The fix is to explicitly list the column names in the query:

INSERT INTO PlasticProducts (sku, product_description, material_type)
SELECT sku, product_description, material_type
FROM Products
WHERE material_type = 'plastic';

The situation can get even worse if a view is created using SELECT *, and later the base tables are modified to add or drop columns.

Note: If a view is create using the SCHEMABINDING option, then the base tables cannot be modified in a way that will affect the view definition.

To conclude, do not use SELECT * in production code! One exception here is when using the EXISTS predicate. The select list in the subquery for the EXISTS predicate is ignored since only the existence of rows is important.