SQL Server Performance Forum – Threads Archive
Distinct or Group by
Should I use DISTINCT or use Group By clause as later also selects distinct items and works faster than DISTINCT ClauseIf you use GROUP BY you may run into limitations, for example you cannot sort on a field or expression that is not in the SELECT clause. You can if you use DISTINCT.
Distinct is (funtionally) faster than a group by and is recommended if you arent going to be doing any aggregates. It can (as it appears in your case) work slower, but its usually less resource intense etc. Chris
I dont Think there is any performace difference between Distinct & Group BY, Bcoz both usually results the same plan. Use of these clauses is depends on your requirement. http://www.winnetmag.com/SQLServer/Article/ArticleID/24282/24282.html Thanks
Sandy
So you’re saying there is occassionally a performance difference then! I’ve certainly seen a performance difference – and either one can be faster than the other in differenct scenarios. Interestingly, if you are writing code that you need to work on Access too, GROUP BY is a better choice as DISTINCT is not available in Access, but then if you have to make your code work in both SQL Server and Access you probably have bigger problems than which of these to use! Dave Hilditch.
Ask a SQL Server Question
www.matiogi.com
Dave, I have to correct you there, because you really can use DISTINCT in a Jet query. You’re probably thinking about DISTINCTROW: that’s a required keyword in Jet UPDATE queries in case you have multiple tables in the FROM clause (as far as I know, as in: that’s the sort of problem that I ran into). If you run SELECT DISTINCTROW MyField FROM MyTable (in Access only), where MyField has recurring values, you will see as many rows as there are in the table, and no grouping. What DISTINCT won’t allow you to do is to include Memo (text) or Binary fields in the SELECT. This is true for both DISTINCT and GROUP BY, and for Jet and SQL Server (note that Jet will consider VARCHAR and NVARCHAR fields over 255 characters as Memo type fields). Performance on SQL Server – well, the execution plans certainly look the same, so if you don;t need to do any aggregates why not use DISTINCT. — Ah, in Access DISTINCT corresponds to setting the "Unique Values" property of the query to Yes, DISTINCTROW corresponds to setting "Unique Records" to True. Only one of the properties can be Yes at a time, but indeed both can be No at the same time. Adriaan
]]>