The Bit Bucket

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

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: Server Tuning Level: Advanced

Question:

SQL Server 2008 introduced a server option for optimize for adhoc workloads.

Can you explain the problem that it is designed to solve, what most commonly causes the problem, and what impact this option has when you enable it?

2021-04-27

In my last T-SQL 101 post, I described the CASE statement. Until SQL Server 2012, that was the only real option that we had for choosing between alternate values. In SQL Server 2012, Microsoft gave us another option with the IIF function.

The IF function is very similar to the IF function in Microsoft Excel. It takes three parameters:

• A boolean value to check (normally this is an expression)
• A value that will be returned if the first parameter is true.
• A value that will be returned if the first parameter is false.

In the main image above, you can see the equivalent CASE statement and the simplified IF function equivalent.

2021-04-26

Another request that I received a while back, for a new function to our free SDU Tools for developers and DBAs, was to be able to find the day of the week, for the same day and month, over a range of years. A simple example would be to find what day Christmas will be each year for the next ten years. So we’ve added a new function WeekdayAcrossYears.

It takes four parameters:

2021-04-23

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: Intro

Question:

Consider the following code:

SELECT CustomerID, CustomerName 
FROM Sales.Customers
WHERE BusinessCategory = 'Cafe'

UNION

SELECT CustomerID, CustomerName
FROM Sales.Customers
WHERE City = 'Toronto';

A: What would be the difference in results if the query was changed to use a UNION ALL instead of the UNION?

2021-04-22