Skip to content

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.

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:

SELECT * 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.

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:

SELECT EmployeeID, FirstName, LastName FROM Employees;

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

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

The rule does not need Analysis Context or SQL Connection.

3 minutes per issue.

Design Rules, Bugs

There is no additional info for this rule.

CREATE VIEW Prooducts
AS
SELECT p.*
FROM Production.Product AS p
ORDER BY Name ASC
UNION ALL
SELECT *
FROM Production.Product p
ORDER BY Name ASC
UNION ALL
SELECT *
FROM Production.Product p
ORDER BY Name ASC
UNION ALL
SELECT *
FROM Production.Product p
WHERE EXISTS( SELECT a.*,b.*,*
FROM Person.Contact AS a
, HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID AND
a.LastName = 'Johnson' )
ORDER BY Name ASC
  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

Analysis Rules