One of my clients recently asked me a very interesting question about the computed column and Compute Scalar Operators. Let us learn about it today.
Question: Do Stream Aggregate Operator Always Need Sort Operator?
Answer: Absolutely No.Before I explain this question a bit more in detail.
Is Stream Aggregate is Same as Gather Streams of Parallelism? – This was one of the brilliant questions I had received during my recent engagement.
In my earlier post on “SQL SERVER – Remove Duplicate Rows Using UNION Operator”, I showed a different method of removing duplicate rows from the result set using a UNION operator. Here is another similar method using EXCEPT operator to find distinct results.
I love query tuning and performance tuning projects. In one of the recent Comprehensive Database Performance Health Check my client had a very interesting scenario where their queries were running pretty fast earlier but gradually started to run slow and eventually started to take over 10 minutes to execute. They reached out to me to tune their slow running query. While looking at the query execution plan, there is a big key lookup operation which was actually slowing down their query. I decided to create an index with include column on that table and resolve this issue.