As a database developer, when I started using Power BI, I was concerned about it retrieving reams of data only to perform transformations downstream. The Power Query editor misleads us into thinking the retrieval and transformations are applied sequentially. Thanks to Query Folding, that’s not usually the case. And that gives us more power to […]
If you’re building a large INSERT statement using VALUES, you might find yourself hitting the limit of 1000 rows, for example: INSERT statement exceeds the maximum allowed number of 1000 row values Folks may tell you there are better ways to solve the issue, but the likelihood is that you’re doing it this way for a particular reason. […]
Dealing with endless formats for SQL scripts is a special type of pain you get to enjoy as a data developer. With the arrival of SSMS v22.7 last week, we have a solution in the form of a T-SQL formatter which is now available in preview. What’s new? The ability to format scripts in the […]
Someone drops a KQL query in your lap and says “you know SQL right?”. Honestly, it’s different enough to trip you up, but similar enough that you’ll pick it up quickly. In this post I want to touch on what KQL is, why it matters, and show how familiar (or different) it is compared to […]
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 […]
Andy Brownsword 