I analysed a huge query in DTA. The database is kind of badly designed and does not have primary keys on tables, also they dont have clustered indexes on them. I found around 80 tables as such. Now, without indexes the queries are obviously going to run slow but when I put them through the DTA, it always asked me create a non clustered index (even on heaps). Something like
CREATE NONCLUSTERED INDEX [_dta_index_IO_Status_MAster_10_1899153811__K2_4_5_1912]
ON [Scorecard].[IO_Status_MAster]
(
[WBS] ASC
)
INCLUDE ([OrderNumber], [IO_STATUS])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
My question, does DTA works on the premise that table has a clustered index on it? Why does it not advise to create a Clustered Index?