For this week’s short post I’ve been inspired by Reddit. There’s something a little sus with this snippet of SQL: The result? ඞ A very simplistic way to produce an illustration. Not quite as complex as other ways to create imagery with SQL Server…
One method for transferring a database to a different environment is using a Data-Tier Application – in the form of a DACPAC (for schema) or BACPAC (for schema and data). Trying to use this approach with multi-database solutions is a challenge though as Data-Tier Applications don’t play nicely with cross-database objects. Let’s look at how we can […]
Categories
Basic Table Metrics
In Management Studio we can view object details by hitting F7 in Object Explorer. It gives us basic metrics but I find it very slow to load for the details I typically need. For that reason I though I’d share a script to turn to for metrics I commonly need. This query returns: Results for the AdventureWorks2022 […]
Categories
Searching with Wildcard Characters
Performing a wildcard search by throwing a % into a LIKE expression is bread and butter. How do we handle this when we actually want to search for the wildcard though? This was an issue I first saw early in my career before I was even building database solutions. The business had a back office solution where you could […]
Categories
Out of the Office
I’m currently out of the office, not quite as pictured unfortunately. I’ve had quite a bit recently – for better or worse. But that’s not why we’re here. I do have a pet peeve though, and I’m sure we’ve all seen it before: Then you take a look at their profile, and it’s just as […]
Categories
Creating a Role for Proc Execution
We have database roles for reading and writing data but interestingly there’s no role which provides permission to execute procedures. Most DBAs I’ve worked with – production or development – prefer to use stored procedures for data access rather than an ORM. A role to allow procedure execution would be very handy. So let’s fix […]
A hierarchy of directories which contain files. That’s how we typically think about file storage. That’s not quite the same everywhere. In Blob Storage a file can appear to be in a directory, but when it’s removed so is the directory. This can occur when using Lifecycle Management to help purge legacy blobs, which can […]
Categories
Archiving Files with Move and Rename in SSIS
Integration workflows will typically involve handling files. As part of that we’ll need to move them around, for example moving into an archive directory. The File System Task component can be used for a variety of operations such as creating directories or copying files. Here we’re going to look at two specific operations to help […]
Last week we looked at using Database Snapshots to help with rolling back upgrades. The snapshot maintained a point in time copy of the database which could be later restored. We can go further – a database can have multiple snapshots. Let’s suppose we want to take one before an upgrade, another once the upgrade is […]
Deploying database changes are relatively easy. Where things get complicated is the rollback. Sure, it’s easy to script out and revert a procedure to a previous version, but what about destructive changes? In this post we’ll look at how we can use Database Snapshots to remove some of the headache that comes with rolling back […]
Andy Brownsword 