Posts

I work a lot with Azure SQL Database, and if you’ve done that, you will have realised that, just like other Azure services, the time zone is set to UTC. Select from GETDATE() or SYSDATETIME(), and you’ll find it’s the current UTC date and time i.e. the same as you’d get from the SYSUTCDATETIME() function.

I can see why that makes sense much of the time. If your users are all over the world, that seems an entirely appropriate setting. Same deal if you’re linking different systems together: it’s good to have a common timeframe.

2020-03-12

I regularly need a list of countries in drop-down lists, etc. To make that easy, in our free SDU Tools for developers and DBAs, we added a tool to do just that. It’s a view called Countries.

It’s a simple view that returns details of the current official list of countries.

For each country, it returns the 3 character country code, the country number, the name of the country and which continent it’s part of.

2020-03-11

I was working with Azure Analysis Services the other day, and was having an issue with the processing of a tabular data model. When I first tried to process, I was receiving an error that told me pretty clearly that the AS server couldn’t connect to the SQL Database. Fair enough. Wasn’t sure what had nuked those credentials but reset them.

Next, I started to get this error: Failed to save modifications to the server. Error returned: ‘The key didn’t match any rows in the table

2020-03-10

Hi Folks,

Just a heads-up that SQL Down Under podcast show 78 with Microsoft Program Manager Pam Lahoud is now out the door. Wasn’t planned but it ended up being great timing for International Women’s Day as well.

In the show, Pam discusses index operations in SQL Server, from standard operations through to the resumable operations that have recently been added and optimized.

You’ll find the show (and podcast subscription details if you want those) here: http://podcast.sqldownunder.com

2020-03-09

In an earlier post, I showed how you could extract the year, month, day, etc. from dates. But these came back as numbers. So instead of “March”, I got 3 returned.

Sometimes though, I want the name of the period, not the number.

Look at this query:

And you can see what it returns here:

This looks easy, but what if I’m not speaking English? It’s important to understand that functions like DATENAME are affected by the regional/language settings that you are currently using in your session.

2020-03-09

I mentioned recently that 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 another one that I did complete on Audible recently was Stories I’d Tell in Bars by Jen Lancaster.

I’ve seen comments about the printed version of this book, that say it’s riddled with typos and grammar/spelling errors but fortunately that wasn’t an issue for me as I listened to the audiobook. The narration of that was fine.

2020-03-06

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