Sql-Server

The DateDiffNoWeekends tool in our free SDU Tools for developers and DBAs, has been really popular. Another request that we received though, was how to calculate which date a particular numbers week day is. For example, we might want to know which is the 12th week day of the month. So we’ve added a tool to do that. It’s called WeekDayOfMonth.

It’s a function that takes three parameters:

@Year is the year (of the month in question) @Month is month in question (as a number within a calendar year i.e. March = 3) @WeekdayNumber is an integer that gives you the position in the month that you are trying to calculate.

2020-02-26

I work with a lot of different software houses. Microsoft calls these ISVs (Independent Software Vendors). They develop applications that we all use day to day.

When I look at their code, many developers have tried to write database-agnostic code. They might be working with SQL Server today, but want to be able to use Oracle, or Cassandra, etc. without changing code.

As a concept, that sounds great. Why would you want to tie yourself into a specific vendor?

2020-02-25

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

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