SA0062B : Check function names used in CREATE FUNCTION statements for following specified naming convention
Introduction
Section titled “Introduction”Use standardized naming conventions for functions to avoid confusion and ensure clarity in multi-developer environments.
Description
Section titled “Description”In SQL Server, properly naming functions is crucial for readability, maintenance, and adherence to naming conventions. Discrepancies in function names can lead to confusion, especially when managing large codebases or collaborating across teams.
For example:
-- Example of a function with a less descriptive nameCREATE FUNCTION GetData() RETURNS TABLE AS RETURN SELECT * FROM TableName;In this example, the function name GetData lacks specificity, causing ambiguity about its purpose or the data it retrieves. Best practices recommend descriptive and consistent naming to enhance understanding and reduce errors.
-
Ambiguous or inconsistent function names can reduce code readability and comprehension among developers.
-
Failure to follow naming conventions may lead to errors in understanding the function’s intended use, especially in large or distributed teams.
How to fix
Section titled “How to fix”To resolve issues with inconsistent function naming patterns, it is important to ensure that function names are descriptive and adhere to established naming conventions.
Follow these steps to address the issue:
1.Review the current function name to determine its purpose and the data it operates on or returns. Use SELECT OBJECT_DEFINITION(OBJECT_ID('FunctionName')) to review the function’s definition if needed.
2.Decide on a more descriptive name that clearly communicates the function’s purpose and usage. For instance, include key information such as the type of data returned or the operation performed.
3.Rename the function using the sp_rename system stored procedure. Ensure the new name aligns with any team or project naming conventions.
4.Update any references to the function within your codebase to prevent errors, which can be done using the global search functionality in SQL Server Management Studio (SSMS) or a source code editor.
For example:
-- Example of renaming a function with a descriptive nameEXEC sp_rename 'GetData', 'GetOrderDetails';The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”| Name | Description | Default Value |
|---|---|---|
| ScalarFunctionNamePattern | Scalar function name pattern. | regexp:ufn[A-Z][A-Za-z1-9_]+ |
| ScalarFunctionSchemaQualifiedNamePattern | Scalar function schema qualified name pattern. | - |
| TableValuedFunctionNamePattern | Table-valued function name pattern. | regexp:ufn[A-Z][A-Za-z1-9_]+ |
| TableValuedFunctionSchemaQualifiedNamePattern | Table-valued function schema qualified name pattern. | - |
| AggregateFunctionNamePattern | Aggregate function name pattern. | regexp:ufn[A-Z][A-Za-z1-9_]+ |
| AggregateFunctionSchemaQualifiedNamePattern | Aggregate function schema qualified name pattern. | - |
| PartitionFunctionNamePattern | Partition function name pattern. | regexp:[A-Z][A-Za-z1-9_]+PFN |
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”8 minutes per issue.
Categories
Section titled “Categories”Naming Rules, Code Smells
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)RETURNS TABLEASRETURN( SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total' FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID WHERE SH.CustomerID = @storeid GROUP BY P.ProductID, P.Name);Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0062B : The function [Sales].[ufn_SalesByStore] does not match the naming convention. The expected key name is [tvfn*[A-Z]A-Za-z1-9*]+]. | 1 | 22 |