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 […]
Most SQL Server environments have more jobs, schedules, and hidden complexities than you realise. It’s only when you arrive at a migration and peek under the hood that the scale is clear. Here we’ll pull out details from msdb to give a clear snapshot of what you’ll actually be dealing with. If you don’t understand the effort […]
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, Database Instructions landed for GitHub Copilot as part of the SSMS v22.3 release. Database Instructions piggyback on extended properties to give Copilot specific context about database objects, which can in turn improve its responses. For me though, this is much more than a step closer to succumbing to AI. I see this as […]
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. […]
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 […]
Andy Brownsword 