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

The topic describes the SA0114B analysis rule.

Message

Object with the same name but different type already exists

Description

The rule checks the script and reports for created objects with the same name as an existing object, but of different kind.

For example, the rule will match table created with the name of already existing or created in the same script schema.

SQL
Copy
1CREATE TABLE [Person].[Person](
2        [BusinessEntityID] [int] NOT NULL,
3        [FirstName] [dbo].[Name] NOT NULL,
4        [MiddleName] [dbo].[Name] NULL,
5        [LastName] [dbo].[Name] NOT NULL,
6        [ModifiedDate] [datetime] NOT NULL)

How to fix

In order to avoid confusion, do not name objects of different type with one and the same name, and rename the different types of objects that have the same name until there is only one object with that name left.

Scope

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

Parameters

Rule has no parameters.

Remarks

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

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Naming Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE AA ( AA INT, BB INT);
 2CREATE TABLE BB ( AA INT, BB INT);
 3CREATE INDEX BB ON AA (AA,BB)
 4CREATE INDEX AA ON AA (AA,BB)
 5ALTER TABLE BB ADD cc int
 6ALTER TABLE BB ADD ClassLibrary2 AS (cc + 1)
 7
 8ALTER TABLE BB ADD Person AS (cc + 1)
 9ALTER TABLE BB ADD PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode AS (cc + 1)
10
11CREATE INDEX PreferredVendorStatus ON AA (AA,BB) -- IGNORE:SA0114B
12CREATE INDEX PreferredVendorStatus ON AA (AA,BB)
13
14CREATE TABLE foo( foo1 int not null)
15CREATE TABLE foo1( foo int not null)
16
17CREATE DATABASE foo;
18CREATE TABLE foo2 (foo VARCHAR(2) NULL);
19
20SELECT foo FROM dbo.foo WHERE foo = 'foo';
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 Database [foo] has same name as a Table [foo] created in the same batch. 17 16
9 SA0114B : The created Column [AA] has same name as a Table [AA] created in the same batch. 1 18
10 SA0114B : The created Column [BB] has same name as a Table [BB] created in the same batch. 1 26
…
See Also

Other Resources

Analysis Rules