Sql-Server

In recent years, I’ve done a lot of work in software houses (Microsoft calls them ISVs or Independent Software Vendors). Many of these software houses have worked out that they won’t be able to just keep selling their on-premises applications because their customers are asking for cloud-based solutions.

And more importantly, the customers want the software houses to manage the applications rather than themselves. So, many of the software houses start trying to turn their on-premises applications into Software as a Service (SaaS) applications.

2019-08-15

Take a look at the following query:

I’ve asked for a list of the databases from the sys.databases view. But rather than executing it against whichever database I was already connected to, I’ve said USE master; to change to the master database.

The command will be sent to the master database instead of any other database that I might have been connected to. At that point, master becomes my “current database”.

2019-08-12

A few months back, I started noticing that many of our clients had started to mention Snowflake.

In recent years, I’ve been in lots of planning and architectural meetings where there was already a presumption that AWS was being used rather than Azure. I put that down to a great selling job by the AWS people who got corporate IT folk locked into large enterprise agreements early. And so no matter what the technical question is, the answer will be something that runs on AWS.

2019-08-09

I’ve got a number of clients in the superannuation (aka retirement fund) industry. At many of these sites, there is a need to be able to do this:

• Using a web site, members must be able to see their own data pretty much 24x7
• Some latency is ok i.e. it might be ok for a member to see data up to yesterday, or up a point in time a few hours ago
• Refreshing the website data is not incremental, truncating the tables and reloading them is required

The challenge is: what happens if a member looks at the data during the time it’s being refreshed?

2019-08-08

In my last SDU Tools post, I described a tool for calculating date dimension columns. While dates are often enough, in many data warehouses, you also need to allocate time periods across each day as well. To make that very easy, in our free SDU Tools for developers and DBAs, we added a tool called TimePeriodDimensionColumns.

This tool is a table-valued function that takes a time (actual time of the day), and the length of each time period, in minutes. In the example in the main image above, I’ve asked for 8:34PM and I’ve said that each period is 15 minutes long.

2019-08-07

Important concepts in any development are reusing code, and abstraction. Look at the first WHERE clause here:

I’ve asked SQL Server to return rows where the CreditRatingID is the one that has the maximum rating. I might not know how to find the maximum rating but if there is code that finds it for me (i.e. dbo.GetMaximumRating()), I don’t need to know that.

This is an example of a function that retrieves a value, and then I can use the value in my own query, without having to copy all the code that’s needed, and without even having to know how it works.

2019-08-05

In a recent post, I talked about how varchar(10) doesn’t mean up to 10 characters, particularly since the introduction of UTF-8 in SQL Server 2019.

So given I do want to limit the number of characters in strings at times, how exactly should I now do that?

A plan takes shape ?

One of my current plans is to do this:

Step 1: Start by forgetting that the number in brackets for the string data types is really relevant to a developer in any way.

2019-08-01

When you’re building a data warehouse using SQL Server, one of the first things that most people start to design is a Date dimension. To make that really, really easy, in our free SDU Tools for developers and DBAs, we added a tool called DateDimensionColumns.

This tool is a table-valued function that takes a date, and returns a set of columns that many will find enough for a good Date dimension. You can see the list of returned columns in the main image above.

2019-07-31

Look at the following query:

I’ve asked SQL Server to return orders where there are order comments. I did that by saying:

OrderComments IS NOT NULL

If I had said IS NULL, I would have returned all the orders with no comments. The interesting concept though, is NULL.

What is NULL?

The first thing to understand is that NULL isn’t a value. That’s why we can’t say:

WHERE OrderComments = NULL or WHERE OrderComments <> NULL

2019-07-29

If you read almost any book on the SQL language, you’ll see definitions where:

varchar(n)

means a varying length character data type, and where n is the number of characters it can store.

SQL Server 2019 changes things

If that’s how you’ve seen it, SQL Server 2019 is going to change your understanding. The product team have pointed out that that n was really the number of bytes, and that “it never was the number of characters”.

2019-07-25