The binary datatype of SQL Server is one of those features most developers don’t really use that often, but it turns out there’s more to binary values than just storing large, non-relational blobs.
Category: By difficulty
Have you tried sp_ctrl3?
I frequently need to look up object definitions when I’m developing or query tuning. You could use Object Explorer in SSMS, but that takes a lot of time and clicking. Then there’s the Alt+F1 shortcut, which will trigger the sp_help stored procedure. That however, comes with a lot of annoying built-in limitations, so a few years ago I started building and maintaining a “better Alt+F1” of sorts.
I decided to call it “Ctrl+3“. But I suppose you could assign it to any keyboard shortcut you want.
A visual representation of SQL Server Agent jobs
If all you have is a hammer, everything will eventually start looking like a nail. This is generally known as Maslow’s hammer and refers to the fact that you use the tools you know to solve any problem, regardless if that’s what the problem actually needs. With that said, I frequently need a way to visualize the load distribution of scheduled jobs over a day or week, but I could never be bothered to set up a web server, learn a procedural programming language or build custom visualizations in PowerBI.
So here’s how to do that without leaving Management Studio.
Availability Groups: How to sync logins between replicas
AlwaysOn Availability Groups are a reasonably simple way to set up disaster recovery (DR) for your SQL Server environment, and with fairly little effort, you can get a bit of high availability (HA) from it as well. But there are a few gotchas, the most obvious of them being that Availability Groups only synchronize specific user-databases, not the entire server setup.
Things that are not included in AGs include logins, SQL Server Agent jobs, SSIS packages stored in SQL Server, linked servers and server settings. You could sync these manually (as is often the case), but wouldn’t you just love to have an automated process do all this for you?
In this post, we’ll look at logins. For the sake of simplicity, I’ll assume that you have a primary replica with a single AG and any number of secondary replicas. The logic holds true if you have multiple AGs, it just gets trickier.
The compelling case for using heaps
I’m an outspoken advocate of always using a clustered index on each and every table you create as a matter of best practice. But even I will agree that there’s a case for using the odd heap now and then.
Not giving a shit about performance is tech-debt
For practically every piece of code you develop, there will be trade-offs. Sometimes, you can combine the best of two worlds, other times it comes down to some hard choices. For T-SQL developers, it typically boils down to a few key questions:
- How much time can you spend perfecting code instead of just shipping?
- Can we just fix it when it becomes a problem?
- Is buying more hardware cheaper than paying for developers to tune their code?
- Is better code harder to read, and will a junior developer be able to work with it?
Human-readable ranges of integers or dates
This is a real-world problem that I came across the other day. In a reporting scenario, I wanted to output a number of values in an easy, human-readable way for a report. But just making a long, comma-separated string of numbers doesn’t really make it very readable. This is particularly true when there are hundreds of values.
So here’s a powerful pattern to solve that task.
Video: three SQL Server join operators in three minutes
In an attempt to try a different approach, here’s a three-minute video explanation of how the different physical join operators in SQL Server work and why you would choose one over the other.
More reading
I’ve written a few blog posts on join operators befores, so if this video wet your appetite, here’s some recommended reading:
I’d love to hear what you think of the short video format! Please leave feedback in the comments below or on Twitter.
Comparing nullable columns
Do you ever compare the values of a lot of columns in two tables? Sure you do. Like, for instance, in a cross update, when you need to figure out which rows you should actually update. But it gets worse if the columns are nullable. The fact that any value could potentially be NULL vastly complicates the comparison and might wreak havoc not only on your code but also on your query performance.
But there’s hope.
Start Management Studio with alternate Windows credentials
If you’re a consultant connecting to remote client servers, or if you have a heterogenous network environment with different Active Directory forests without established trust relationships, you’ll have a few extra challenges connecting to SQL Server using Windows authentication, and SQL Server authentication may not be available.