Microsoft® SQL Server 2008 R2 Unleashed

Provide Explicit Column Lists

When writing SELECT or INSERT statements in application code or stored procedures, you should always provide the full column lists for the SELECT or INSERT statement. If you use SELECT * in your code or in a stored procedure, the column list is resolved each time the SELECT statement is executed. If the table is altered to add or remove columns, the SELECT statement returns a different set of columns. This can cause your application or SQL code to generate an error if the number of columns returned is different than expected. For example, consider the following sample table:

create table dbo.explicit_cols (a int, b int) insert explicit_cols (a, b) values (10, 20)

Now, suppose there is a stored procedure with a cursor ...