The DISTINCT clause in a query can help us quickly remove duplicates from our results. Sometimes it can be beneficial to stop and ask why. Why do we need to use the clause, why are we receiving duplicates from our data? I see this typically due to a JOIN being used where we don’t really want all of those results. This could […]
Deadlocks are an enduring feature of SQL Server. They’ve been a source of pain for many over the years and there are various ways to diagnose, mitigate or resolve them. Here I want to demonstrate approach I haven’t seen discussed – using an application lock to segregate processes. An example where I’ve used this effectively […]
In the previous post we looked at the functions TRY_CAST, TRY_CONVERT, and TRY_PARSE and how they compared. I wrapped up and said that my preference for new developments would be to use TRY_PARSE due to the tighter control which it provides us. As with everything in SQL Server however, there is no ‘best’ approach, it depends. I therefore wanted […]
In the previous post we looked at how ISNUMERIC and TRY_CAST work and why we may want to utilise the latter when building validation for our data. When SQL Server 2012 rolled around it wasn’t only TRY_CAST which was added, we also had TRY_CONVERT and TRY_PARSE introduced too. Here we’re going to look at how […]
Data validation is key when ingesting from external sources. As we can’t always be certain of data quality we inevitably find bad data which needs to be handled. Here I wanted to look at a couple of options for validating numeric data. Here’s the scenario – we’ve got data which may have been received via […]
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 […]
Andy Brownsword 