I'm only vaguely familiar with SQL Server's Database Engine Tuning Advisor (DTA) but I'm using it to suggest recommendations for indexes on my database.
Most of the tables in the database at present have a single index which is the Primary Key. It is defined during the tables' creation as:
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([ID] ASC)
When the DTA runs, it is advising that I add additional non-clustered indexes on some tables containing only the [ID] column, e.g.:
CREATE NONCLUSTERED INDEX [_dta_index_Employee_18_1170844725__K1] ON [dbo].
[Employee]
(
[ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
What I'm wondering is why it's recommending I create an index which exactly matches an index that already exists. Is there some benefit to the query optimizer of there being a non-clustered index as well as the clustered one?
For what it's worth, in most instances the [ID] column is also an IDENTITY
column.