Distinct sort operator in SQL Server execution plan

Distinct sort operator in a SQL server execution plan is used to remove duplicates from a result set. First it sort the data and then eliminate the duplicate records. This operator is typically used when a query includes a distinct clause or when SQL server needs to ensure uniqueness in the result set. The distinct sort operator is more efficient than other method to eliminate the duplicate records.

Icon of distinct sort operator

See the example

For the demo we are creating at table and inserting some records

CREATE TABLE Sales_Demo (     SaleID INT PRIMARY KEY,     ProductID INT,     Quantity INT,     SaleDate DATE );  INSERT INTO Sales_Demo (SaleID, ProductID, Quantity, SaleDate) VALUES     (1, 101, 5, '2023-01-01'),     (2, 102, 3, '2023-01-02'),     (3, 101, 5, '2023-01-01'),     (4, 103, 2, '2023-01-03'),    (5, 101, 5, '2023-01-01');

Let’s run the below query

SELECT DISTINCT ProductID FROM Sales_Demo;

See the execution plan.

  

To remove the Distinct Sort we need to create index on the productid as below.

CREATE INDEX IX_Sales_Demo_ProductID ON Sales_Demo(ProductID);

After creating the index we are running the same query.