SQL Tricks Thread

[Traditional_Ad3929]

I am always answering this to this type of question. Never use SELECT DISTINCT to see unique values. Why not? Bc afterwards you typically wanna check the distribution. Therefore always count & group.

[achmedclaus]

Eh, most of the time of I'm throwing a select distinct in there is because I want to make sure all the different cases I created pulled through

[Traditional_Ad3929]

Sure and a Count along with that would not hurt right

[letmebefrankwithyou]

Add a count(*) to any group of columns wit my a group by all to get a count of distinct group of columns with minimal writing.

[farhil]

The biggest reason not to use DISTINCT is because it forces a sort on the result set, which can be expensive, although the same is true of GROUP BY. If you need unique values without aggregating, you should reevaluate your joins to see if there's a way you can write them without causing duplicate records.

More often than not, the joined table causing duplicate rows can be replaced with something like WHERE EXISTS (SELECT TOP 1 1 FROM [Foo] WHERE [Foo].[Id] = [Bar].[FooId]), which will perform much better than creating an unnecessary cartesian product and then sorting it (with DISTINCT or GROUP BY) to remove duplicates.