The Bit Bucket

Book Review: SQL Server Query Tuning and Optimization

I was pleased to be sent a pre-release copy of Benjamin Nevarez’s new book SQL Server Query Tuning and Optimization. Last time, I reviewed his High Performance SQL Server book.

This book seems to be somewhat new and somewhat an update, but this time with the main focus on query tuning and optimization. That’s pleasing as the main way to get better performance out of SQL Server is to fix the queries. much more so than anything to do with the hardware or server configurations that so many people focus on.

2022-08-05

SDU Tools v22 is now available (finally)

One of our popular free resources is the SDU Tools library. If you haven’t checked it out, I’d encourage you to do so. It’s a large library of functions, procedures, and views all written in native T-SQL code.

You can easily use it as a complete library, or use it as examples of how to write T-SQL code. v22 is now available for download.

If you aren’t on our notification list, you can add yourself here:

2022-06-05

Why is Greg holding a book about a duck?

One weekend many years ago, my youngest daughter Erin was looking for something to do. She was a very creative child so I suggested “why don’t you write a book?”

She said she could write one, if she only had a title. I told her that you could write a book about almost any title. I randomly picked:

What the duck didn’t see

(with the emphasis on didn’t)

To get her started, I wrote some content, then asked her to continue. She did the same, and then I wrote some more. I turned out to be quite fascinating. I had no idea where she was taking the story and I’d be excited to read what she’d written. Along the way, my eldest daughter Kirsty wrote some content as well. My second daughter Andrea’s name was used for the main person in the story.

2022-01-25

Azure SQL Database now has an improved STRING_SPLIT !

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

ADF: Use MSIs not SQL Logins whenever possible

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

Fix: The parameters supplied for the procedure sp_set_firewall_rule are not valid.

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

New Online Course Released: Advanced T-SQL for Developers and DBAs

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

Reliably dropping a SQL Server database if it exists

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

Book: Implementing Power BI in the Enterprise

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

MVP Challenge: Data and AI plus some online exams

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