We’re on quite a roll with window functions these past few weeks. Last week we looked at the operators we’d see in execution plans when using a window function. This week I wanted to tackle one of the more troublesome ones specifically: the Sort operator. We know that sort operators are expensive in our queries. To use […]
After last week’s post I got to thinking that although we’ve looked at how to implement window functions, we haven’t peeked under the hood to see how they are executed. Then what do you know, Kevin has picked up my post to provide an alternative approach. He observed that whilst his approach was more straightforward it produced a more […]
We revisited window functions last week for T-SQL Tuesday. As we’re in that area there’s another example I thought was worth exploring. Can we group data whilst applying window functions in the same query? The problem For our example we’ll use the same sample data as last week. This contains sales values broken down by period, also […]
Our SQL Server environments are home to many workloads. In these situations one size doesn’t always fit all when it comes to configuration. Let’s take an example. On one hand we want small, high performance, transactional processing during the day. Through the evening this could change to larger, batch processing tasks. I’ve seen the configuration […]
We’ve recently looked at how single threaded concurrency was handled in SQL Server and followed up last time by diving into multithreaded concurrency. This time we’re going to go a bit further with the multithreading to look more into how the contention is handled when the number of cores available doesn’t line up with our workload. We’ll also […]
If writing code is the meat of our work then then packaging our stored procedures for deployment is the bread and butter to complete the sandwich. Deploying them is key so here we’ll be looking at options for how to effectively script those changes. The specific challenge we’re looking at here is how to change […]
When building data solutions we’ll be handling sets of data which may need to be imported, transformed, aggregated or exported amongst other things. With more complex solutions can come a need to share this data between different areas of the process. Last week we looked at being able to share individual values within a session and […]
When developing application code we may be familiar with exchanging data and objects through the application. This practice is much less common in SQL due to being transactional when we consider application / OLTP workloads. There are however some times when exchanging values between code in the same session which is what we’ll start to […]
A few weeks back we looked at options for storing multiple boolean flag values in a table. One of the options we considered was the use of a Bit Mask where we packed multiple flags into a single field. When we have individual fields within a table, using and manipulating those is straightforward. However when we’ve got […]
Whilst we have front-end code which requires faster response times, there are also times we build code with the purpose of running for extended periods of time. Examples of this could be maintenance routine, or custom built queuing mechanisms. The way in which we design longer running processes like this can be key when it […]
Andy Brownsword 