I’ve just began considering optimizing my queries through indexes because SQL information is growing large and fast. I checked out the way the optimizer is processing my query with the Execution plan in SSMS and observed that the Sort operator has been used. I’ve heard that the Sort operator signifies a poor design within the query because the sort can be created prematurely with an index. Here is definitely an example table and data much like what I’m doing:

IF OBJECT_ID('dbo.Store') IS NOT NULL DROP TABLE dbo.[Store]
GO

CREATE TABLE dbo.[Store]
(
    [StoreId] int NOT NULL IDENTITY (1, 1),
    [ParentStoreId] int NULL,
    [Type] int NULL,
    [Phone] char(10) NULL,
    PRIMARY KEY ([StoreId])
)

INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 0, '2223334444')
INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 0, '3334445555')
INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 1, '0001112222')
INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 1, '1112223333')
GO

Here's a good example query:

SELECT [Phone]
FROM [dbo].[Store]
WHERE [ParentStoreId] = 10
AND ([Type] = 0 OR [Type] = 1)
ORDER BY [Phone]

I produce a non clustered index to assist accelerate the query:

CREATE NONCLUSTERED INDEX IX_Store ON dbo.[Store]([ParentStoreId], [Type], [Phone])

To construct the IX_Store index, I begin with the easy predicates

[ParentStoreId] = 10
AND ([Type] = 0 OR [Type] = 1)

I Quickly add the [Phone] column for that ORDER BY and also to cover the Choose output

So even if the index is made, the optimizer still uses the type operator (and never the index sort) because [Phone] is sorted AFTER [ParentStoreId] AND [Type]. Basically take away the [Type] column in the index and run the query:

SELECT [Phone]
FROM [dbo].[Store]
WHERE [ParentStoreId] = 10
--AND ([Type] = 0 OR [Type] = 1)
ORDER BY [Phone]

Then obviously the type operator sits dormant through the optimizer because [Phone] is sorted by [ParentStoreId].

So now you ask , how do i create a catalog which will cover the query (such as the [Type] predicate) and do not have the optimizer make use of a Sort?

EDIT:

The table I am dealing with has a lot more than 20 million rows