So developers came to me with an entity framework query to see if I can add some indexes to optimize the call. I was hesitant since the query is rather ugly but that's not for here. So I added a non-clustered index with two primary columns and one included column to help alleviate a table scan. When the query is ran without the added NC index, it uses the clustered index in the query plan which is on the pk field (identity column) of the table, I get three rows returned. When I add the index, only two are returned. I am not filtering the index at all. I've never seen this before but wondering if it has to do with null data in one of the columns? Here is the query: SELECT Distinct1.C1 AS [C1] , Distinct1.C2 AS [C2] , Distinct1.C3 AS [C3] FROM ( SELECT DISTINCT 1 AS [C1] , CAST(Limit1.Part_ItemID AS NVARCHAR(MAX)) AS [C2] , CASE WHEN ( Extent3.SecondItemNumber IS NULL ) THEN N'' ELSE Extent3.SecondItemNumber END + N' (' + CASE WHEN ( Extent3.ItemDesc_CONCAT IS NULL ) THEN N'' ELSE Extent3.ItemDesc_CONCAT END + N')' AS [C3] FROM ( SELECT TOP ( 7 ) Extent1.Part_ItemID AS [Part_ItemID] FROM bnwc.WH_BOM_Part AS [Extent1] INNER JOIN bnwc.WH_Item AS [Extent2] ON Extent1.Part_ItemID = Extent2.ItemID WHERE ( Extent2.SecondItemNumber LIKE '%6825%' ) OR ( Extent2.ItemDesc_CONCAT LIKE '$6825%' ) ) AS [Limit1] LEFT OUTER JOIN bnwc.WH_Item AS [Extent3] ON Limit1.Part_ItemID = Extent3.ItemID ) AS [Distinct1]; Here is the index I added: CREATE NONCLUSTERED INDEX [NC_IDX_WH_Item_ItemID] ON [bnwc].[WH_Item] ( [SecondItemNumber] ASC, [ItemDesc_CONCAT] ASC ) INCLUDE ( [ItemID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO Anyone have any explanation as to why the record count would be different with and without the NC index?
Sorry, lost track of this thread. So after doing some more digging, it became a little more clear to me that I think what I was missing is that in the select top 7 subquery, there is no order by statement involved. But when I add an index to the table, now that data is sorted and so when the subquery runs and uses that index, the data is being returned in a different order and would affect the top level query.