The Bit Bucket

There was a question on Stack Overflow recently about SQL Server being “stuck” in single user mode.

The poster had a SQL Server 2016 instance, running on Windows Server 2016. He said he tried to do this:

  ALTER DATABASE MyDatabase
  SET MULTI_USER;

But it always told him that the database is in use.

The fix

The core concept to understand here is that when a database is in single user mode, you can’t assume that you’re the single user. In this case, he clearly wasn’t.

2020-02-20

While it’s not 100% required, your life is generally better if your databases are set to the maximum database compatibility level that your server supports. But it’s a bit of a pain to actually make that change. So in our free SDU Tools for developers and DBAs, we have a procedure that does just that. It’s called SetDatabaseCompabilityForAllDatabasesToMaximum.

It’s a stored procedure and takes no parameters at all.

It checks all the user databases and sets them to the maximum level allowed by the server that they are attached to. It is applicable to SQL Server only. (This procedure doesn’t exist in the Azure SQL Database version of the tools).

2020-02-19

I don’t like seeing abbreviations used when there’s no need to have them. Abbreviations can be overused and many times they’re cryptic. Worse, I often see them applied inconsistently.

In general, I try to avoid abbreviations unless they are on a tight list of ones that I use all the time.

A pet dislike of mine is seeing them used (even if consistently) for interval values in T-SQL functions like DATEADD, DATEDIFF, etc.

2020-02-18

When you’re working with T-SQL, you’ll often need to add time periods onto a date, perhaps onto today’s date. You might be adding (or subtracting) days, or hours, or minutes, or months, and more.

The DATEADD function is the one that we can use add or subtract intervals to the date and time. Now in the example shown here, I’ve started with 20190228, or 28th February 2019. I want to add on 12 days:

2020-02-17

I was working with a client the other day and we had what we thought was a simple situation:

• A table in an SSAS tabular model project
• The table had two partitions
• We needed to remove 6 columns from the table

So, we’d already removed the 6 columns from the SQL Server view that the tables were being loaded from. We just needed to update the tabular model.

Now for an unpartitioned table, that’s easy. You open the table properties, click Design, wait for a moment, click below the query, then on the Query menu, click the option to Refresh. When you then click Import, you can then save the changes. We do that all the time.

2020-02-14

It’s common to need to get a table of all dates between two dates, and in our free SDU Tools for developers and DBAs, we have a function DatesBetween that I’ve previously talked about. But sometimes it’s not a start and end date that you have, it’s a start date and a number of intervals. For example, I might want all the dates for the next three months, or three weeks, or two years. To make that easy, we’ve added a function called DatesInPeriod.

2020-02-12

SQL Server Reporting Services (SSRS) is a wonderful product, that’s still really important, even though the product group in Microsoft hasn’t been showing it much love lately.

I was at a client site the other week, and while we were using Power BI (PBI) for their dashboards and general visualizations, we were looking to use SSRS for part of the solution.

What fascinated me, is that when they talked to the local Microsoft field staff, they kept being told how SSRS was old technology, and asking why they’d look to use such old technology.

2020-02-11

Sometimes we have a date and we need to extract components of the date. For example, we might have a date and wonder what’s the year or we have a date and wonder what the month is or the day.

While there are several ways to extract the year, the month, and/or the day from a date in SQL Server, there are functions designed to do precisely that.

The YEAR function takes a date and extracts the year. The MONTH function takes a date and extracts a month (as a month number from 1 to 12). And the DAY function takes a date and returns the day (again as a day number like 28, not the name of the day like Tuesday).

2020-02-10

I haven’t been doing as much driving in my car lately, so that’s limited the time I’ve had for listening to audio books. But one that I did complete recently was AI Super Powers by Kai-Fu Lee. The subtitle is China, Silicon Valley, and the New World Order.

This is an area that I’ve been really passionate about for the last few years.  I can see AI changing so much of our current world, and much sooner than I think most people will realise.

2020-02-07

I just wanted to give everyone a heads-up that we recently shipped version 18 of our free SDU Tools for developers and DBAs.

We’ve had so many requests for a version of SDU Tools that will work with Azure SQL Database. So, in version 18.0, we added that support.

So what’s changed?

We’ve added two new scripts included in the download: one for adding the tools to Azure SQL DB and one for removing them.

2020-02-05