Skip to content

SA0062B : Check function names used in CREATE FUNCTION statements for following specified naming convention

Use standardized naming conventions for functions to avoid confusion and ensure clarity in multi-developer environments.

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 name
CREATE 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.

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 name
EXEC sp_rename 'GetData', 'GetOrderDetails';

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

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

The rule does not need Analysis Context or SQL Connection.

8 minutes per issue.

Naming Rules, Code Smells

There is no additional info for this rule.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
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
);
  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

Analysis Rules