Inline index definition fails when name is the same as table name

Question

SQL Server objects i.e. tables and indexes have their own namespaces. So it is possible to have the same name for index and table(but it's not a common/good practice):

CREATE TABLE t(id INT PRIMARY KEY, col INT);
CREATE INDEX t ON t(col);

SELECT * FROM sys.tables WHERE name = 't';
SELECT * FROM sys.indexes WHERE name = 't';

db<>fiddle demo

Unfortunately I am not able to create the same construct using inline index definition:

CREATE TABLE t(id INT PRIMARY KEY, col INT, INDEX t(col));

Msg 2714 Level 16 State 5 Line 1

There is already an object named 't' in the database.

-- below code is working correctly
CREATE TABLE t(id INT PRIMARY KEY, col INT, INDEX t1(col));

db<>fiddle demo 2

Do I miss something obvious or is it a bug?

Dyna

. . This would appear to be a bug. Of course, I tend to name indexes as "idx_<tablename>_<columns>". That makes it much easier to understand messages with the index name. – Succubus

@GordonLinoff Yes, I agree that proper naming convention is crucial. I am just curious why seperate statements are ok while inline version ends with error. I also think it is a bug, but first I want to exclude most obvious reasons :) – Dyna

What version is your database? As much as I agree this isn't a good naming convention it worked without any issue on my 2014 instance. I don't have anything newer readily available. – Donnell

@SeanLange demo SQL Server 2014 Now I am pretty sure it is a bug :) – Dyna

Well that is odd. Worked in 2014 but fails in 2017....seems like a bug to me. – Donnell

Answer 1

Do I miss something obvious or is it a bug?

Looks like a bug.

CREATE TABLE t(id INT PRIMARY KEY, col INT, INDEX t(col));

outputs

Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64) 
    Jul 21 2018 07:47:45 
    Copyright (C) 2017 Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 17763: ) (Hypervisor)


(1 row affected)

Msg 2714, Level 16, State 5, Line 4
There is already an object named 't' in the database.
Msg 1750, Level 16, State 1, Line 4
Could not create constraint or index. See previous errors.

Please add a feedback item here: https://feedback.azure.com/forums/908035-sql-server Especially noting that this is a regression in SQL 2016.

Belay

It was my intention from the beginning but first I always ask for opinion. I will create a case and link to this question. Thanks :) – Dyna

For future reference: feedback.azure.com/forums/908035-sql-server/suggestions/… – Dyna