[SOLVED] Create Index without name while creating table

Create Index without name while creating table

I have following temporary table with index. This works since I explicitly name it as IX_#Final_1.

How do I create the index without explicit name?

CREATE TABLE [#Test1]
(
    [ID] INT IDENTITY(1, 1) PRIMARY KEY,
    [Member_BK] VARCHAR(20),
    [Company] VARCHAR(20),
    [Facility_Name] VARCHAR(200),

    INDEX [IX_#Final_1] ([Member_BK], [Company])
)

I need this because of the issues outlined in dba.stackexchange.com - There is already an object named PK_MyPrimaryKey in the database ( primary index on temp table)

Solution

I need this because of the issues outlined in dba.stackexchange.com - There is already an object named PK_MyPrimaryKey in the database ( primary index on temp table)

No you don't. This fails (with error "There is already an object named 'PK1' in the database.").

DROP TABLE IF EXISTS #T1, #T2

CREATE TABLE #T1 ([ID] INT CONSTRAINT PK1 PRIMARY KEY)

CREATE TABLE #T2 ([ID] INT CONSTRAINT PK1 PRIMARY KEY)

As it creates a constraint and a corresponding object in sys.objects - where object names have to be unique within a schema.

This doesn't

DROP TABLE IF EXISTS #T1, #T2

CREATE TABLE #T1 ([ID] INT NOT NULL INDEX IX UNIQUE CLUSTERED)

CREATE TABLE #T2 ([ID] INT NOT NULL  INDEX IX UNIQUE CLUSTERED)

Index names only need to be unique within a table - there is no issue with multiple different tables existing concurrently that share index names. Many of the built in objects share fairly unimaginative index names such as clst as can be seen from.

SELECT name, 
       COUNT(*), 
       type_desc
FROM sys.indexes
GROUP BY name, 
         type_desc
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

SQL Server 2000 didn't even have this restriction and allowed duplicate index names within a table. If you were in this position resolving this was a required change before upgrading to 2005.