SA0063B : Check view names used in CREATE VIEW statements for following specified naming convention
Introduction
Section titled “Introduction”Use consistent and descriptive naming conventions for views to ensure clarity and maintainability.
Description
Section titled “Description”Naming conventions for views in T-SQL are crucial for maintaining readability and manageability of SQL Server databases. Inconsistent or unclear view names can lead to confusion, making it harder for developers and administrators to understand the purpose and use of each view. This ultimately impacts the efficiency and accuracy of database management and usage.
For example:
-- Example of a view with a non-descriptive nameCREATE VIEW View1 ASSELECT * FROM Customers WHERE Active = 1;This query demonstrates a poor naming convention. Using a non-descriptive name like View1 does not convey the intent or focus of the view, which can cause confusion when revisiting the code or when collaborating with others.
-
Hard to identify the purpose or logic of views, leading to increased cognitive load and potential errors.
-
Challenges in adhering to best practices for database standards and making maintenance tasks more complex.
How to fix
Section titled “How to fix”Ensure that view names conform to the established naming conventions to improve comprehension and maintainability of the database.
Follow these steps to address the issue:
1.Analyze the existing view name and identify if it aligns with your organization’s naming conventions. For example, views that represent active customers could be prefixed with vw_ followed by a descriptive name.
2.Use the sp_rename system stored procedure to rename views with non-descriptive names to more meaningful ones. Ensure that the new name accurately reflects the content or purpose of the view.
3.Review and update any dependencies that might reference the old view name, such as stored procedures, functions, or applications, to ensure they use the new name.
For example:
-- Example of renaming a view to follow naming conventionsEXEC sp_rename 'View1', 'vw_ActiveCustomers';The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”| Name | Description | Default Value |
|---|---|---|
| NamePattern | View name pattern. | regexp:v[A-Z][A-Za-z1-9_]+ |
| SchemaQualifiedNamePattern | Schema qualified name pattern. | - |
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 VIEW dbo.CustomersAS--Select from local member table.SELECT *FROM CompanyData.dbo.Customers_33UNION ALL--Select from member table on Server2.SELECT *FROM Server2.CompanyData.dbo.Customers_66UNION ALL--Select from mmeber table on Server3.SELECT *FROM Server3.CompanyData.dbo.Customers_99Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0063B : The view [dbo].[Customers] does not match the naming convention. The expected key name is [v[A-Z][A-Za-z1-9_]+]. | 1 | 16 |