Failover Groups for Managed Instances are a great option to replicate data, but they don’t replicate key instance elements – one of which is logins that live in the master database. If left unchecked, failovers leave systems unable to connect and panic ensues. To alleviate this we’ll look at a script to synchronise logins and […]
Having TOP (1) return multiple rows feels wrong… but that’s what WITH TIES can do. For a long time I used patterns like this to get the first record in a group: Then I found the cleaner alternative: Let’s dive into what it is, and why you (probably) shouldn’t use it. WITH TIES The WITH TIES syntax was added over 20 years ago but it […]
Last week I demonstrated a fast binary search approach to quickly slice through large unindexed tables. I love the approach, but it fell short in two key areas – drop-in usage, and proper boundary handling. Why Let’s recap what we’re doing here: Large append-heavy tables – like logs or audits – often don’t have a useful index […]
I know, clickbait right? Hear me out. Searching vast log or audit tables without indexes is painful. Narrowing down a specific time range often means scanning millions or billions of rows. But that doesn’t have to be the case. Approach This approach is designed for tables with two particular characteristics: The combination of these characteristics […]
Last week we looked at how expressions are evaluated with the ISNULL and COALESCE functions. Whilst we’re in the area it’s worth running through how data types are selected for them too. It might have implications with how you implement them. The way these functions choose which data type to return differs, so they aren’t direct swap outs for each other, […]
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 […]
Andy Brownsword 