The Bit Bucket

I get pretty excited about new T-SQL enhancements. Back in 2016, I was so pleased to see Microsoft finally add a string split option to T-SQL, but my enthusiasm was limited when I saw how it was implemented. Now that’s mostly fixed !

While it’s possible to build functions that did string splitting just like I wanted, the problem is that no matter how you implemented them, they were really just too slow. And most importantly, much slower than a built-in function would be.

2021-11-05

Azure Data Factory (ADF) is great for moving data around. It often needs to connect to SQL databases. If you’re creating linked services for this, please try to avoid using SQL logins. Don’t use usernames and passwords for authentication if you can avoid them.

Azure SQL Database lets you can create a user from a data factory’s managed service identity (MSI). MSIs are a special type of Service Principal (SP). They provide an identity where you don’t need to manage the ID or any sort of password.  Azure does that for you.

2021-09-26

I often use the procedure sp_set_firewall_rule to set firewall rules for Azure SQL Server. (There’s a similar call to set the firewall for databases). The other day though, I got an error that had me puzzled:

I also tried it with named parameters and got the same error.

When I looked at my previous scripts, I realised that I had used a Unicode string for the first parameter previously.

2021-08-25

I’m really pleased to let you know that our latest online on-demand course is now released:

Advanced T-SQL for Developers and DBAs

To celebrate the release, you can get 25% off the pricing until Aug 14th by using coupon code ATSRELEASE

We’ve had so many requests from customers to bring this course to our online platform. It was always one of our most popular in-person courses, and it’s now released and fully updated.

2021-07-26

I often need to write scripts that drop and recreate databases. The hard part of that has always been reliably dropping a database if it already exists. And no, you wouldn’t think that would be hard, but it is.

Built in Command

T-SQL has a built-in command for this.

DROP DATABASE IF EXISTS Sales;

You’d hope that would work, but it doesn’t.  I wish it did. The problem is that it will fail if anyone is connected to the DB. And to check if anyone is attached, you first need to check if the DB exists, so it makes the whole “IF EXISTS” part that was added to this command, completely pointless.

2021-06-24

It’s been a while coming, but my latest book is now out. Implementing Power BI in the Enterprise is now available in both paperback and eBook. The eBook versions are available in all Amazon stores, and also through most book distributors through Ingram Spark distribution.

I’ve had a few people ask about DRM-free ePub and PDF versions. While the Kindle version on Amazon is their normal DRM setup, you can purchase the DRM free version directly from us here:

2021-06-18

If you follow anyone that’s part of Microsoft’s MVP program, you might have heard there has been a global cloud skills challenge happening lately: #TheMVPChallenge.

There were three challenges that each of us could complete:

• Azure Data & AI Challenge
• Dynamics 365/Power Platform Challenge
• Microsoft 365 Challenge

You can imagine which one I chose to complete. I did the Azure Data and AI challenge. Data and AI are a pretty common grouping.

2021-05-07

Camtasia is one of my favourite products. I use it regularly. I’ve been so excited to start to get to use Camtasia 2021 that was released just recently. It’s a nice step up from an already great product.

But what I didn’t like after upgrading, is that every time I started Camtasia, instead of the “normal” editing screen, I got a cutesy little popup that asked me what I wanted to do with the product today.

2021-04-30

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

UNION and UNION ALL are commonly used to combine two sets of rows into a single set of rows.

EXCEPT is another set operator.

Can you explain what it does?

Answer:

EXCEPT is used to remove any rows in the first set of rows, if the same rows appear in the second set.

2021-04-29

Azure SQL (both Azure SQL Database and Azure SQL Managed Instance) both have different password complexity rules to SQL Server. I was reading an email discussion list and a poster asked where he could find the list of password complexity rules for Azure SQL. I said I’d never seen a list.

Well it turns out that there is a list, but not where you might have thought to look. They’re spelled out in this article:

2021-04-28