The Bit Bucket

When you’re working with T-SQL, it’s common to need to work out how far apart two dates and or times are. Look at the query below:

In this case I’m asking how many days is it from 28th of February 2019 to 31st of July 2019? And if look at the answer:

We can see that it’s 153 days.

Now I used days here but now we could have used any of those other intervals that I discussed when we looked at DATEADD. So I could work at how many minutes that was or how many seconds that was between the two times. I could even say how many months?

2020-02-24

I’ve been working a lot with ADF (Azure Data Factory) again lately. If you’ve started to work with ADF and aren’t using Git in conjunction with it, you need to change that now.

ADF objects are also represented as JSON objects and lend themselves nicely to being stored in Git.

Another key advantage is that if you don’t have Git connected, when you’re working with ADF, you don’t have a Save button. If you’re part way through making changes, you can either Publish them or Discard All. When you have Git in place, you get an option to Save.

2020-02-21

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