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

Avoiding SELECT * ensures that your queries are efficient, maintainable, and secure, contributing to better overall database performance and application stability.

Description

The common practice of using * in SELECT statements can lead to issues. While it is convenient for retrieving all columns, this approach can negatively impact both performance and maintenance.

Example of a problematic query:

SQL
Copy
1SELECT * FROM Employees;

In this example, using * retrieves all columns from the Employees table, which may include unnecessary data. This can increase the load on the database server, result in higher network traffic, and make application maintenance challenging when table structures change.

  • Performance degradation: Fetching all columns, even unused ones, increases data volume that SQL Server processes and sends over the network.

  • Maintenance challenges: Changes in table or view definitions, such as adding or removing columns, can break application compatibility.

How to fix

Avoid using the asterisk (*) in SELECT statements to improve query performance and maintainability. Instead, explicitly specify the column names you need.

Follow these steps to address the issue:

  1. Identify the columns needed by the application. Determine which specific columns are required for your query results.

  2. Replace the asterisk (*) in your SELECT statement with the identified column names.

  3. Update any existing queries that use * to ensure they are aligned with the current structure of the table.

Example of a corrected query:

SQL
Copy
1SELECT EmployeeID, FirstName, LastName FROM Employees;

Scope

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

Parameters
Name Description Default 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.

Effort To Fix
3 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
  Message Line Column
1 SA0011 : SELECT * in stored procedures, views and table-valued functions. 3 10
2 SA0011 : SELECT * in stored procedures, views and table-valued functions. 7 8
3 SA0011 : SELECT * in stored procedures, views and table-valued functions. 11 8
4 SA0011 : SELECT * in stored procedures, views and table-valued functions. 15 8
See Also

Other Resources

Analysis Rules