SA0114B : Object with the same name but different type already exists
Introduction
Section titled “Introduction”Database object name conflicts can cause confusion and unpredictable behavior.
Description
Section titled “Description”In SQL Server, having multiple database objects with the same name but different types within the same schema or database can lead to confusion and errors. This issue occurs when a new object is inadvertently given the same name as an existing object, but they serve different purposes-such as having a table and a stored procedure with the same name.
For example:
-- Problematic example of object naming conflictCREATE TABLE [Person].[Person]( [BusinessEntityID] [int] NOT NULL, [FirstName] [dbo].[Name] NOT NULL, [MiddleName] [dbo].[Name] NULL, [LastName] [dbo].[Name] NOT NULL, [ModifiedDate] [datetime] NOT NULL);
-- Potential conflict if a stored procedure with the same name existsCREATE PROCEDURE [Person].[Person] ASBEGIN -- Some operationEND;Such confusion can lead to:
-
Difficulties in maintaining and understanding the database schema, as developers might mistake one object type for another.
-
Operational errors during execution, as SQL Server might resolve object names unpredictably, leading to unintended behavior.
How to fix
Section titled “How to fix”Ensure unique naming for different types of database objects to avoid confusion and potential errors in SQL Server.
Follow these steps to address the issue:
1.Identify any objects with conflicting names within the same schema or database. Use SELECT * FROM sys.objects WHERE name = 'ObjectName' to locate these objects.
2.Determine a naming convention to differentiate object types. For example, prefix table names with tbl_ and stored procedures with sp_ .
3.Rename the objects to follow your chosen convention. Use sp_rename for tables and ALTER PROCEDURE for stored procedures.
For example:
-- Rename table to avoid conflictEXEC sp_rename 'Person.Person', 'Person.tbl_Person';-- Rename stored procedure to avoid conflictALTER PROCEDURE [Person].[sp_Person] ASBEGIN -- Some operationEND;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”5 minutes per issue.
Categories
Section titled “Categories”Design Rules, Naming 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 TABLE AA ( AA INT, BB INT);CREATE TABLE BB ( AA INT, BB INT);CREATE INDEX BB ON AA (AA,BB)CREATE INDEX AA ON AA (AA,BB)ALTER TABLE BB ADD cc intALTER TABLE BB ADD ClassLibrary2 AS (cc + 1)
ALTER TABLE BB ADD Person AS (cc + 1)ALTER TABLE BB ADD PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode AS (cc + 1)
CREATE INDEX PreferredVendorStatus ON AA (AA,BB) -- IGNORE:SA0114BCREATE INDEX PreferredVendorStatus ON AA (AA,BB)
CREATE TABLE foo( foo1 int not null)CREATE TABLE foo1( foo int not null)
CREATE DATABASE foo;CREATE TABLE foo2 (foo VARCHAR(2) NULL);
SELECT foo FROM dbo.foo WHERE foo = 'foo';Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0114B : The created Index [PreferredVendorStatus] has same name as existing Column [Purchasing].[Vendor].[PreferredVendorStatus]. | 12 | 13 |
| 2 | SA0114B : The created Column [ClassLibrary2] has same name as existing Assembly [ClassLibrary2]. | 6 | 19 |
| 3 | SA0114B : The created Column [Person] has same name as existing Schema [Person]. | 8 | 19 |
| 4 | SA0114B : The created Column [Person] has same name as existing Table [Person]. | 8 | 19 |
| 5 | SA0114B : The created Column [PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode] has same name as existing Constraint [PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode]. | 9 | 19 |
| 6 | SA0114B : The created Index [BB] has same name as a Table [BB] created in the same batch. | 3 | 13 |
| 7 | SA0114B : The created Index [AA] has same name as a Table [AA] created in the same batch. | 4 | 13 |
| 8 | SA0114B : The created Index [PreferredVendorStatus] has same name as a Index [PreferredVendorStatus] created in the same batch. | 12 | 13 |
| 9 | SA0114B : The created Database [foo] has same name as a Table [foo] created in the same batch. | 17 | 16 |
| 10 | SA0114B : The created Column [AA] has same name as a Table [AA] created in the same batch. | 1 | 18 |
| … | |||
| 12 | SA0114B : The created Column [foo1] has same name as a Table [foo1] created in the same batch. | 14 | 18 |
| 13 | SA0114B : The created Column [foo] has same name as a Table [foo] created in the same batch. | 15 | 19 |