There is a query which uses 5 columns. It's a part of a very big query which is not viewable to post here.
SELECT TOP 1 docnum
FROM IdentityDocs idd1 with (index (IIdentityDocsDocIssued))
WHERE c2.ClientId=idd1.ClientId AND idd1.DocStatusId=1 AND (idd1.DocTypeId=7 OR idd1.DocTypeId=8)
ORDER BY idd1.DocIssued DESC
It wasn't covered with index, and there was Index spool and Sort operation in the execution plan. Since we couldn't change the query, we had to implement a new index.
CREATE NONCLUSTERED INDEX [IIdentityDocsDocIssued] ON [dbo].[IdentityDocs]
INCLUDE ( [DocNum])
Now that we have the new index, I want to know if it's better to have the index hint (with (index (IIdentityDocsDocIssued)) ) or not to have it in the query.
In case we remove the hint, and if the statistics get out of date, will the Query optimizer use the new index? Currently the statistics are updated and there is no difference in the execution plan with and without the index hint.
There is a Clustered index on Clients table, and before the new index "IIdentityDocsDocIssued" was created, the optimizer was using the clustered index introducing index spool and sort in the execution plan of the sp.
I want to know your thinking for using or not the hint.
Igor Micev,SQL Server developer at Seavuswww.seavus.com