How T-SQL Thinks? Sorting: Order By, Joins, Group By?

I started out my morning delightfully forgetful yesterday’s research mission.

As I fed my cats, made coffee, drove to work, and began settling into my day, I intermittently listened (and re-listened) to the first third of Episode 29 of SQL Data Partners Podcast with Carlos L Chacon. This episode is called, “You Thinking What I Am Thinking?” and it’s about how SQL Server “thinks.” The interview is with Brent Ozar, and I think it’s a really interesting interview even to a newbie like me whose understanding of SQL Server / T-SQL is so limited.

From the perspective of a person in my position who wants to turn out queries (often based on unique, one-time, complex requests for years’ worth of data from multiple tables) with results in the most finished way possible, it was a bit distressing to hear Mr. Ozar talk about how applying an ORDER BY to a query is like asking SQL Server to go through an the white pages in an old phone book and manually re-order all the names by first name – and how that would require going through and scribbling all the names down as you went to get them in the right order. .

On Sorting and Joins-

Mr. Ozar mentioned that when you join tables together, even if there isn’t an ORDER BY in the query, there is an “implicit order by” created by the join. Uh oh! In our group, we rarely have a need to run a query that doesn’t involve a few joins.

On Sorting and Group By –

I mentioned this podcast episode to my boss this morning as something he might also be interested in – and he wanted to know if GROUP BY was included in this general CPU strain scenario. My answer right now? I don’t know but it makes sense to me if Joins and ORDER BY would. Googling gave me very some rather complex results (for where I am.)

I don’t yet understand indices at all in SQL Server, and it looks like they play a significant role in the way these things work. I am looking forward to getting a better understanding of these things, and will report back.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s