|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer|
SR0001: Avoid SELECT * in stored procedures, views, and table-valued functions
If you use a wildcard character in a stored procedure, view, or table-valued function to select all columns in a table or view, the number or shape of returned columns might change if the underlying table or view changes. The shape of a column is a combination of its type and size. This variance could cause problems in applications that consume the stored procedure, view, or table-valued function because those consumers will expect a different number of columns.
You can protect consumers of the stored procedure, view, or table-valued function from schema changes by replacing the wildcard character with a fully qualified list of column names. You can easily expand the wildcard character by using refactoring. For more information, see Expand Wildcard Characters in SELECT Statements.
The following example first defines a table that is named [Table2] and then defines two stored procedures. The first procedure contains a SELECT *, which violates rule SR0001. The second procedure avoids SELECT * and explicitly lists the columns in the SELECT statement.
CREATE TABLE [dbo].[Table2] ( [ID] INT NOT NULL IDENTITY(0, 1), [c1] INT NOT NULL , [Comment] VARCHAR (50) ) ON [PRIMARY] CREATE PROCEDURE [dbo].[procWithWarning] AS BEGIN -- Contains code that breaks rule SR0001 SELECT * FROM [dbo].[Table2] END CREATE PROCEDURE [dbo].[procFixed] AS BEGIN -- Explicitly lists the column names in a SELECT statement SELECT [dbo].[Table2].[ID], [dbo].[Table2].[c1], [dbo].[Table2].[Comment] FROM [dbo].[Table2] END