Skip to content

SA0114B : Object with the same name but different type already exists

Database object name conflicts can cause confusion and unpredictable behavior.

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 conflict
CREATE 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 exists
CREATE PROCEDURE [Person].[Person] AS
BEGIN
-- Some operation
END;

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.

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 conflict
EXEC sp_rename 'Person.Person', 'Person.tbl_Person';
-- Rename stored procedure to avoid conflict
ALTER PROCEDURE [Person].[sp_Person] AS
BEGIN
-- Some operation
END;

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

Rule has no parameters.

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

5 minutes per issue.

Design Rules, Naming Rules, Bugs

There is no additional info for this rule.

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 int
ALTER 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:SA0114B
CREATE 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';
  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

Analysis Rules