Sql-Server

In “Progamming 101”, developers learn that sprinkling hard-coded numbers and strings throughout their code, is a really lousy idea. Yet I regularly see T-SQL code that’s littered with hard-coded numbers and “special” string values. I really wish we could avoid that. It would greatly improve code quality, and improve debugging for procedures.

Take the following code as a really simple example:

SET @NextInterval = @CurrentInterval * 1440;

It’s not at all obvious why the value 1440 is there. In this case, it was the number of minutes in a day. Today, we can do this:

2020-03-05

I often need to calculate a day number in a month. For example, I need to work out the 3rd Tuesday of a month, or the 4th Friday, and so on. To make that easy, in our free SDU Tools for developers and DBAs, we added a tool to do just that. It’s a function called DayNumberOfMonth.

It’s a function that takes four 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) @DayOfWeek is an integer with Sunday = 1, Monday = 2, etc. @DayNumber is also an integer for the day number (i.e. 3 for 3rd Monday)

2020-03-04

On our SQL Down Under email list today, someone asked:

My title is DBA but my job is more into SQL Developer, fixing data involved in applications. Do you think if I study Power BI that I can get a better job?

I get asked this sort of question regularly, particularly from traditional DBAs who see their roles disappearing.

The most basic answer is to adapt what you’re doing across to roles that are still in need like data modelling, query performance tuning, DB design in general, etc. However, I wanted to make some more broad recommendations for those considering something more radical.

2020-03-03

In an earlier post, I showed how you can use the YEAR, MONTH, or DAY functions to extract those parts of a date. But what if you want to extract any one of those other intervals?

Well, that’s what the DATEPART function does. It allows us to specify the interval that we want to extract. Look at the following query examples:

And here are the results of that query:

2020-03-02

If you have worked with larger tables in either Power BI or Analysis Services, you’ll know that developing against them is painful. Any time the data needs to be refreshed (which happens often), you spend a lot of time sitting around waiting for it to happen.

Now our buddy Chris Webb proposed a way of getting sampled data during development. That’s pretty good. And fellow MVP Marc Lelijveld also uses a similar method.

2020-02-28

I’ve mentioned before that I really don’t mind working with large tables. They’re only slow to use if you’re looking through all the rows when trying to find the data that you want. But there is still sometimes a valid need to archive data from a main table, across into an archive table. And people often run into problems, particularly blocking problems, when they try to do that.

Let’s start with an example pair of tables (much simplified for clarity):

2020-02-27

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