When eliminating NULL values with SQL Server queries we typically reach for ISNULL or COALESCE to do the job. Generally speaking they’ll provide equivalent results, but they work in different ways. If you’re dealing with logic more complex than ISNULL(ColA, ColB) – for example using a function or subquery as part of the expression – then you might be in for a surprise. […]
String parsing is a regular occurrence when it comes to data engineering. Splitting strings, converting, combining – all in an effort to bring some order. Sometimes we need a more powerful tool, and that would be Regular Expressions (Regex). Regular expressions are something I have a fondness for under the right conditions, and with them being introduced […]
I don’t use RAISERROR often – I usually forget which severity code to use. After looking at a sprinkling of them recently I decided it was time for a refresher, so come along for the ride. If you check out the online documentation it states that “New applications should use THROW instead”. It also sounds like its used to raise […]
A question I ask myself often when exploring unfamiliar data sets. So here’s a quickie: If you’re in need of a quick measuring stick, this should do the job. Microsoft does note that the row_count field is an approximate value in this DMV. It’s taken from the internal table PARTITIONCOUNTS which can be cached, but […]
Handling data modifications to large tables can be tricky. We can end up with long running queries and large transactions. Efficiently handling them comes from batching. Effective batching in general helps us by: In this post we’ll take a look at a key-range approach to solve the issue. With this solution a batch will be […]
Code is an ever moving target. Version control and documentation only go so far, if they even exist. Sometimes all you have is the code in front of you. This is why I always start stored procedures with a header. Here’s a template as a starter: Name seems redundant as we have the proc name below […]
Sometimes you want to segment records. It may be splitting a customer base for marketing purposes, or segmenting a user base for a new feature. Good segmentation makes clean divisions in the data. In this post we’ll see a way to achieve that with a great deal of help from Window Functions. This post was […]
Implicit conversion happens in SQL Server when the engine detects a mismatch in data types and automatically converts from one type to another. This can be helpful as it makes different types interchangeable and is generally transparent to the client, but it can come with issues. Usually the downside from implicit conversion is seen through […]
When running queries, reviewing execution plans, or diving into Query Store it’s not uncommon to see missing index recommendations, particularly for more complex queries. But are they any good? There are many factors when considering an index. However if you’re a situation where these missing index recommendations are enticing, I wanted to cover 3 key areas you […]
When looking to increase performance for a database it can be the smaller and faster queries which can be improved easier and more effectively than their slower counterparts. We can be drawn to tuning long running and complex queries, but these can be a challenge due to their size and complexity. Sometimes though we can reclaim just […]
Andy Brownsword 