Carry Over Sort vs Batch Mode Window Functions
Video Summary
In this video, I delve into an interesting query optimization topic that has been relevant for years but is now less pertinent due to the advent of batch mode in SQL Server. I explore why certain complex query syntaxes were necessary before batch mode existed and how they can be replaced with simpler, more efficient methods today. Specifically, I demonstrate the carry-over sort method—a technique often used when window functions weren’t available—and contrast it with modern approaches using window functions. By running sample queries in SQL Server Management Studio, I illustrate why the old method is not only slower but also less accurate for certain scenarios. The video aims to provide insights into query optimization and help viewers understand how leveraging batch mode can significantly improve performance without resorting to outdated techniques.
Full Transcript
Over the years, there’s been a lot of very interesting and intricate query syntax ginned up to deal with row mode performance issues that don’t really exist anymore in a world where batch mode exists. One of them is called the carry over sort. I don’t know if you’ve maybe ever run into a problem like this or whether you’ve ever stumbled upon this problem. I admit that the name is a little outlandish, but it comes up a lot when you’re dealing with data analysis type work. And, you know, prior to there being batch mode, there was one specific way that you typically wanted to write queries if you wanted to sort of emulate what what a windowing function would give you for finding the max value for a row, not per column in a row, but just per row, that batch mode largely solves. So, I’m going to talk about that today. Once we get over to SQL Server Management Studio, I’m going to show you probably why the query you think that you want to write is not right. Just how slow, how the process of the process of doing this using row mode is and how batch mode improves that. But of course, we’re going to contrast that with the carry over sort method, the way that it has been presented. This is not my syntax. It’s something that I’ve run across and I just wanted to make sure that I was comparing it accurately to modern versions of SQL Server. Of course, you may still need the carry over sort method of doing this if you are using standard edition of SQL Server. The reason for that is because if you are using standard edition of SQL Server, at least as of the recording of this video, Microsoft still does not think that you have paid them enough to be your friend and they have deliberately hobbled anything involved in the way that you have paid them enough to be your friend.
there’s nothing to build. Yo, of course, otherwiseulanning you have final version of SQL flagship and will be rewrite your friend or of Spain. you have had made all the power졌uj assodo.com Be it would be an exact same way that you have posted positive feedback button to the world And that is now patreon.com buradazt neuron will понять how to run substitute feedback of how to run with optimal name in optimal name know if you want to contribute to this channel you can sign up for a membership link down in the video description for that it’s just a show of appreciation for all this cool content that i write and record for free you can also like comment subscribe and ask me questions for my office hours episodes if you need sql server can help consulting help boy howdy i do all this stuff and as always my rates are reasonable if you want to get all my performance tuning training i have 24 hours of it for about 150 us dollars uh you can in that in that lasts you for life you get it forever and always uh just go to that url plug in that discount code which is also a fully assembled link down yonder and you can you can you can start your learning today uh my new t-sql course uh the beginner content is all done and recorded it’s about 23 hours over 69 modules uh if you are going to attend kendra little and i’s uh t-sql past pre-cons you will get access to this material and uh right now the course is on pre-sale for 250 bucks it will go up to 500 bucks when the course is fully recorded after the summer i am hard at work on all the advanced material now so that isn’t that isn’t that spectacular for you how hard i work uh if you would like to see me live and in person i am going on tour with the red gate road show uh the pass on tour dates new york august 18th and 19th dallas september 15th and 16th and utrecht not just an art supply store it’s a hamlet in the netherlands october 1st and 2nd and then of course past data community summit in seattle november 17th to 21st so with that out of the way uh let’s let’s pratty or party whatever it is so i have uh pre-run a couple things here uh the first the first two things that i have pre-run are uh the the the the version of this query that a lot of people uh will write uh or maybe tried to write at some point in the in the far distant past and we’re unhappy with the performance of uh which is basically to uh select some stuff within a CTE and most importantly in that CTE generate a row number and the goal of the row number is for each post type id because that’s what we’re partitioning by we want to order by uh the the creation date uh converted to just a date it’s a date time we’re just converting it to a date then ordered by owner i user owner user id descending and then ordered by id descending with id descending acting as a bit of a tiebreaker because id is unique and these other things are not guaranteed to be unique either individually or in concert the second thing that i’ve i’ve run is a query that this is hinted to use uh optimizer compatibility level 140 because i do not want batch mode on rowstore to kick in and be enabled for this query the second thing i’ve included with the first uh first running of things is the query that a lot of people think could replace this but this is not the right query to write for this because what this is doing is it’s getting you the max value for each column grouped by post type id that is not correct correct because the the the real sort of algorithm if you want to call it that is that we are ordered first by creation date then ordered by owner user id then ordered by id this is getting the max for each of those individually the first thing that i want to show you is the results because showing you the results shows you where these two methods uh no longer agree as far as uh the included data goes so uh post type id one the owner user id is different between these two uh it’s also different it’s also a different owner user id for post type id two and post type id well three of course right there um the ids are different for these two as well uh so really the the max method just does not give you the correct results there’s just too much different in here focusing over on the execution plan uh we can ignore this one because this one does not give us the correct results so let’s just get that like way out of the way we’re not thinking about this one at all this is the row mode version of the query and you’ll see if you follow along the operator times here this thing runs for just about well a little over 14 seconds i like to say 15 seconds that feels good to me so let’s just say this thing ran for like 15 seconds right nice nice fizz buzzy number there uh and and like really you know there’s just not a whole lot to say about this generally uh in row mode queries like this are quite painful even if you have a reasonable index for sql server to use to make the the window function go faster um you you often it’s often just a terribly inefficient way of writing and running the query so what what i want to show you next is uh the carry over sort method so what the carry over sort method aims to do is get the max you see the max starts here and the max ends way down here but what the max is doing is basically assembling a string based on the getting the max of all three of the columns that we care about the normal carry over sort thing uh does not include as much complexity uh in uh for the second column as mine does the problem that i was running into is that there are negative owner user ids in the post table and sure i could have filtered them out but that’s that’s cheating a little bit uh we want to maintain uh all user ids we want to make sure that we even include the negative ones because what when the max is when the max is a negative number then we we need to consider that don’t we we can’t just not return a result for a row because we didn’t feel like dealing with some abnormal potential abnormalities in the data so i have a case expression here and the case expression just says when owner user id is less than zero then i add some x’s to the left uh which um is different from what we’re doing when owner user id is greater than zero or greater than or equal to zero uh because with that we are right padding the number so we’re like adding like zeros to the right of it the reason that i did the x’s is because if i zero padded it things would have gotten messed up down in the select query in the select query uh we are basically asking for a substring and converting the substring to the correct data type so if i were when you do that with the id column and you have a zero padded number and you convert a zero pad right zero padded number to an integer it’s no problem right because like we just you just remove the zeros from the front of it and you give give the rest of the number the problem with the negative number is that you have you essentially have a string and if i added zeros in then i wouldn’t know if there were naturally occurring zeros in the number uh so i used x’s and i replaced the x’s to avoid confusion there but the carryover sort method just does this we still group by post type id we’re just we’re using the max function across like we’re we have three different columns in the max function uh post type id sorry creation date owner user id and id and like we’re assembling like the max across those three right so max encapsulates all three of those columns and if i run this query and we got a little bit of highlighting to do here don’t we uh this will return correct results at least if we at least compared to the first query that we ran with the window function and uh so like all the results here match what we get from the window function version but we get it much faster what’s cool here is that none of this query even though we’re in compat level 160 uses batch mode the scan of the post table happens in row mode you can see that just sort of over right next to my big head there uh this compute scalar also happens in row mode the hash match aggregate which is totally eligible for batch mode still goes in row mode uh so basically and like and of course parallel exchanges like gather streams don’t support row mode so this whole thing finishes in about two seconds without using any batch mode whatsoever so back before batch mode was really a cool useful thing this was a good method to get the max value per row like i said earlier this can still be very useful if you’re on standard edition because microsoft you didn’t if you’re on standard edition microsoft doesn’t think that you paid them enough to have your queries perform well so you don’t get like even if you’ve got batch mode to like batch mode to happen here you would be limited to a dop of two for your batch mode queries so you would not be like like i have max dop set to eight for this this thing will have used like a dop of eight for uh the query right you can see degree of parallelism right there eight so this thing would have used uh eight cores up for for this whole thing and spreading the workload of 17 million rows out across eight across eight cores nice and efficient is pretty efficient for row mode uh and for batch mode but like 17 million rows across two threads even in batch mode you’re likely going to see some performance fall off there like whatever the like i don’t know maybe maybe you’ll get real lucky and the trade-off won’t be too terrible but um that the standard edition limitations there are really quite a pain the next thing that we’re going to that i want to show you is the uh the the window function version of this again but without that compat level uh 140 restriction on it down here to prevent batch mode on rowstore for kicking in so if we run this query this will take about two seconds now right we get back the correct results this finishes in 1.8 seconds just like the carryover sort method that i showed you above and we don’t have to write that crazy max syntax where we convert like dates and numbers to strings and pad things and all that other stuff with the the synthesis developer edition which is the enterprise edition equivalent skew i’m not using uh 2025 standard developer edition because why would i hurt myself that way um that we we are we are just using regular uh edition here regular developer edition here so this runs at a degree of parallelism of eight and this runs nice and quickly and efficiently so if you’re writing uh window fun so like you know like really the the idea of this window function right it’s like we just want to get like the top row for each post type id so i’m filtering to where row number equals one for all these if it’s if your goal is to find like the max value of something for a row based on whatever criteria i just kind of picked three columns at random from the post table that seemed to like make sense i guess i could have thrown score in there if we felt like it um but uh if you’re doing that like i still would have had to deal with potentially negative numbers because scores can be negative in the post table so that like that maybe that wouldn’t have saved me too much time or trouble with the carryover sort syntax but uh if your goal is to like sort of find data like this and you’re filtering to where row number equals one batch mode can make these queries crazy fast like you don’t even have to add indexes like just let batch mode on rowstore kick in read from your table and batch mode process all the data in batch mode like it’s a way better uh way of running like big data crunchy queries like this if but if you don’t if you’re not in a situation where batch mode on rowstore can kick in for you like if you’re not on sql server 2019 plus and you’re not on enterprise edition and your database is an incompatibility level 150 or better and there’s no batch mode on rowstore uh like naturally occurring and you know like depending on if you can change stuff like you know you can you can mess around with like you know columnstore indexes uh you can mess around with uh putting like a like just like having an empty table in your database with a clustered column store index or creating a temp table with a clustered columnstore index and like left joining to that thing uh you can do all sorts of stuff to get like partial batch mode on rowstore but it it does not go as deep into your query plans as batch mode on rowstore like the intelligent query processing optimizer feature does just kind of weird i think but it’s just something that you learn to live with when you are tuning queries across a variety of strange environments anyway uh i this is just something that caught my interest and i felt like talking about um uh i i hope you enjoyed yourselves i hope you’ve learned something i hope maybe there was some good educational point in this video that you are able to take away from it even if it’s not uh memorizing the the crazy syntax in here uh i feel like perhaps there were a good a few good educational moments aside from that but anyway thank you for watching and i will see you over in the next video adios you
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.