I have a table whose definition looks like this:
CREATE TABLE Budget_Cost_Centers
(
CostCenterIDX INT NOT NULL,
Name VARCHAR(100) NOT NULL,
...
CONSTRAINT PK_Budget_Cost_Centers
PRIMARY KEY CLUSTERED (CostCenterIDX ASC)
);
(some columns were left out because they are not relevant)
I now run the Database Tuning Advisor on a query against this table and it suggests:
CREATE NONCLUSTERED INDEX [_dta_index_Budget_Cost_Centers_5_1079674894__K1]
ON [dbo].[Budget_Cost_Centers] ([COSTCENTERIDX] ASC)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
Which is obviously the same as the existing primary key. Any idea why it would suggest this? Trying to make sure I'm not missing something about how SQL Server works as opposed to the advisor suggesting something ridiculous because that's just the limitations of automated tuning analysis.
More info: I broke down the query that was producing this recommendation to the bare essentials:
select
d.PhaseIDX,
sum(d.YTDActual) YTDActual
from
Budget_Cost_Centers cc
join
Budget_Cost_Centers mc on cc.MasterCostCenteridx = mc.CostCenterIDX
join
Budget_Detail d on cc.CostCenterIDX= d.CostCenterIDX
group by
d.PhaseIDX
The table self-references and the query author joined it to itself in order to filter the records to only those that contained said reference. Changing the query to the following equivalent results in the advisor no longer making the recommendation:
select
d.PhaseIDX,
sum(d.YTDActual) YTDActual
from
Budget_Cost_Centers cc
join
Budget_Detail d on cc.CostCenterIDX = d.CostCenterIDX
where
cc.MasterCostCenterIDX is not null
group by
d.PhaseIDX
Here is the execution plan with the original sql:
