I have table where the PK is a Guid (clustered index) and Name (string, non clustered index) in SQL Server. Every time I run the query with the WHERE and ORDER BY, it only uses the clustered index and omits the non clustered index, and the names are not in ascending order too.
The query is
exec sp_executesql N'SELECT TOP(@__p_3) [c].[Id], [c].[Name]
FROM [Company] AS [c]
WHERE [c].[IsDeleted] = CAST(0 AS bit) AND (CASE
WHEN [c].[Name] >= @__p_0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END & (CASE
WHEN [c].[Name] > @__p_0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END | (CASE
WHEN [c].[Name] = @__p_1 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END & CASE
WHEN [c].[Id] > @__p_2 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END))) = CAST(1 AS bit)
ORDER BY [c].[id],[c].[Name]',N'@__p_3 int,@__p_0 nvarchar(300),@__p_1 nvarchar(300),@__p_2 uniqueidentifier',@__p_3=3,@__p_0=N'Salmaan and CO',@__p_1=N'Salmaan and CO',@__p_2='04D3EEC3-2A91-42E2-3463-08DADF2F5363'
But when I the query making the Name as the first in the ORDER BY and then ID, it works and returns the expected results along with the clustered and non clustered index
exec sp_executesql N'SELECT TOP(@__p_3) [c].[Id], [c].[Name]
FROM [Company] AS [c]
WHERE [c].[IsDeleted] = CAST(0 AS bit) AND (CASE
WHEN [c].[Name] >= @__p_0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END & (CASE
WHEN [c].[Name] > @__p_0 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END | (CASE
WHEN [c].[Name] = @__p_1 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END & CASE
WHEN [c].[Id] > @__p_2 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END))) = CAST(1 AS bit)
ORDER BY [c].[Name],[c].[id]',N'@__p_3 int,@__p_0 nvarchar(300),@__p_1 nvarchar(300),@__p_2 uniqueidentifier',@__p_3=3,@__p_0=N'Salmaan and CO',@__p_1=N'Salmaan and CO',@__p_2='04D3EEC3-2A91-42E2-3463-08DADF2F5363'
The problem with the second approach is it has a higher execution cost even the data is correct. The first query has less cost, but the data is wrong as the ORDER BY on Name is not applied. Please help