Same column with multiple index names. It is possi

问题:

For me there is no need/intention to create multiple index names for a same column. When i'm studying about index i tried the following test. What is the use of the same? Is there any feature?

What i have tried is:

create index id on TestIndex(EmployeeMumber)
create index id1 on TestIndex(EmployeeMumber)
create index id2 on TestIndex(EmployeeMumber)

The result is: Command(s) completed successfully.

Thanks

回答1:

SQL Server doesn't prevent you from creating duplicate indexes (though there has been an unsuccessful push to change this). The "won't fix" is probably largely for backward compatibility reasons - there were probably use cases in previous versions where people would create a new index before dropping an old one, back when options like DROP_EXISTING or rebuilding online didn't exist. As new features in the engine evolve, such as INCLUDE columns, filtered indexes, etc. it is also more complex to determine whether or not two indexes are, in fact, duplicates.