Click or drag to resize

SA0011 : SELECT * in stored procedures, views and table-valued functions

The topic describes the SA0011 analysis rule.

Message

SELECT * in stored procedures, views and table-valued functions

Description

This rule checks stored procedures, functions, views and triggers for use of '*' in column lists of SELECT statements. Though use of '*' is convenient, it may lead to less maintainable applications. Changes to table or view definitions may cause errors or performance decrease. Using the proper column names takes less load on the database, decreases network traffic and hence can greatly improve performance.

As an example, the following syntax is not recommended:

SQL
1SELECT     *
2FROM       dbo.Books

It is recommended to have an explicit list whenever the column to retrieve is known to the application.

SQL
1SELECT     b.b_id ,
2           b.b_title
3FROM       dbo.Books AS b

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
NameDescriptionDefault Value
IgnoreWhenInExistsClause

The parameter controls if to ignore the SELECT * statement when it is inside EXISTS clause.

yes

IgnoreWhenSelectFromTempTable

The parameter controls if to ignore the SELECT * when the table source is a locally created temporary tables.

yes

IgnoreWhenSelectFromCTE

The parameter controls if to ignore the SELECT * when the table source is a CTE.

yes

IgnoreWhenSelectFromTableVariable

The parameter controls if to ignore the SELECT * when the table source is a table variable.

yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1CREATE VIEW Prooducts
 2AS 
 3SELECT  p.*
 4FROM  Production.Product AS p
 5ORDER BY  Name ASC
 6UNION ALL
 7SELECT  *
 8FROM  Production.Product p
 9ORDER BY  Name ASC
10UNION ALL
11SELECT  *
12FROM  Production.Product p
13ORDER BY  Name ASC
14UNION ALL
15SELECT  *
16FROM  Production.Product p
17WHERE  EXISTS( SELECT  a.*,b.*,*
18               FROM  Person.Contact AS a
19               , HumanResources.Employee AS b
20               WHERE  a.ContactId = b.ContactID AND
21                      a.LastName = 'Johnson' )
22ORDER BY  Name ASC

Analysis Results

 MessageLineColumn
1SA0011 : SELECT * in stored procedures, views and table-valued functions.310
2SA0011 : SELECT * in stored procedures, views and table-valued functions.78
3SA0011 : SELECT * in stored procedures, views and table-valued functions.118
4SA0011 : SELECT * in stored procedures, views and table-valued functions.158
See Also

Other Resources