DISTINCT OPERATOR

Maahi 2017-12-21 20:41:08

Hi All,

Is there a better way to rewrite below code without using DISTINCT operator. Other thing, is that I don't see any duplicates inside the table. But not sure in future if we get duplicate combination. that could be reason why some developer has written such type of code. Can we re-write this code in a better way??

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

Thank you.

M

Aaron Bertrand 2017-12-21 20:44:40
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.)
Maahi 2017-12-21 20:54:39
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]

Aaron Bertrand 2017-12-21 20:57:15
Did you compare query results with/without DISTINCT?
Maahi 2017-12-21 21:05:16
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)

Aaron Bertrand 2017-12-21 21:15:02
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).
Maahi 2017-12-21 21:17:47
Okay. Thank you.
Hugo Kornelis 2017-12-21 20:57:57
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.
Maahi 2017-12-21 21:15:57
Got it. Thanks Aaron and Hugo for the help.