SA0011 : SELECT * in stored procedures, views and table-valued functions
Introduction
Section titled “Introduction”Avoiding SELECT * ensures that your queries are efficient, maintainable, and secure, contributing to better overall database performance and application stability.
Description
Section titled “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:
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.
How to fix
Section titled “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:
SELECT EmployeeID, FirstName, LastName FROM Employees;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “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
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”3 minutes per issue.
Categories
Section titled “Categories”Design Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”CREATE VIEW ProoductsASSELECT p.*FROM Production.Product AS pORDER BY Name ASCUNION ALLSELECT *FROM Production.Product pORDER BY Name ASCUNION ALLSELECT *FROM Production.Product pORDER BY Name ASCUNION ALLSELECT *FROM Production.Product pWHERE 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 ASCAnalysis Results
Section titled “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 |