DISTINCT OPERATOR

Why is there a TOP (1) without ORDER BY? You want exactly one row but you don't care which one? Once you add an ORDER BY to get predictable results, you can drop the DISTINCT. There is no difference – if you only return one row, what duplicate rows are you eliminating? (Of course that changes if you use WITH TIES and your ORDER BY leaves the possibility for ties.)

There is ORDER BY Aaron. Sorry my bad.

SELECT DISTINCT TOP (1) [x].[DateDrawn]
,[x].[EquipmentLife]
,[x].[LubricantLife]
,[x].[DateRegistered]
,[x].[SampleID]
FROM [LubeAnalyst].[Sample] AS [x]
WHERE ([x].[SampleStatusID] = 1)
–AND ([x].[ComponentID] = @__componentId_0)
ORDER BY [x].[DateDrawn] DESC
,[x].[EquipmentLife] DESC
,[x].[LubricantLife] DESC
,[x].[DateRegistered] DESC
,[x].[SampleID]

Did you compare query results with/without DISTINCT?

When I do compare with/without DISTINCT operator ,

WITHOUT DISTINCT = 50%
WITH DISTINCT = 50%

Don't know the reason why 50-50!!
I thought with distinct will be little expensive? Why is it so? attaching plan "distinct.sqlplan"

SELECT TOP (1) [x].[DateDrawn]
,[x].[EquipmentLife]
,[x].[LubricantLife]
,[x].[DateRegistered]
,[x].[SampleID]
FROM [LubeAnalyst].[Sample] AS [x]
WHERE ([x].[SampleStatusID] = 1)

SELECT DISTINCT TOP (1) [x].[DateDrawn]
,[x].[EquipmentLife]
,[x].[LubricantLife]
,[x].[DateRegistered]
,[x].[SampleID]
FROM [LubeAnalyst].[Sample] AS [x]
WHERE ([x].[SampleStatusID] = 1)

Plans are identical. SQL Server's optimizer is smart enough to throw away the DISTINCT because it knows you are only retrieving one row anyway. It will also throw away the DISTINCT if certain unique columns are in the SELECT list (e.g. the primary key). Try again with SELECT TOP (10) x.EquipmentLife and I think you'll see the estimated costs are now different with and without DISTINCT (however estimated cost of the overall plan is an often useless and sometimes misleading metric, do don't put too much faith into it).

Okay. Thank you.

As Aaron said, because the TOP will limit the results to just one row the DISTINCT does nothing. There can never be duplicates in a single row.

Got it. Thanks Aaron and Hugo for the help.