The Bit Bucket

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

I don’t know if it’s an Australian “thing” but whenever I’m setting up new financial or government accounts, these organizations insist on actually sending things to our street address.

Given how rampant identity theft has become in many places, this is just not sensible.

[caption id=“attachment_5424” align=“alignnone” width=“538”] Awesome image by Mathyas Kurmann[/caption]

Take a look at letterboxes that people have on local streets, and please try to convince me how sending anything there, is in any way safer than sending it to a post office box.

2019-07-31

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

One of the depressing things about watching TV is that producers (and in particular Hollywood-based producers) seem to have a very skewed concept of what happiness comes from.

Here’s a bit hint: it’s not from wealth or fame.

I’ve made enough trips around the Sun to know that anyone who believes what that TV is telling them, is being conned, big time.

Now I’m not saying that being poor is fun either. It’s not. In particular, anyone who’s deeply in debt would understand that they have very little control of their own lives.

2019-07-26

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

When you look at a SQL Server build number (actually a “Product Version”, you’ll see they have a format like this:

14.0.3256.1

The first number is the major version number. The second number is the minor version number. The third number is the build number. The fourth number is the release number.

These numbers are a little awkward to work with directly in T-SQL so, in our free SDU Tools for developers and DBAs, we added a set of tools that can do that for you.

2019-07-24

Three years back, with much fanfare at a partner conference, Microsoft announced the Microsoft Professional Degree program. It was going to be a set of courses that you could take that would lead to one of their professional degrees.

Now here in Australia, you can’t just call something a degree, and I’m guessing that’s the same in the USA, so I wasn’t surprised when I noticed soon after I started with it, that the name had changed to the Microsoft Professional Program (MPP), and they’d dropped the “degree” word.

2019-07-23

In the previous T-SQL 101 post, I showed the TOP operator. One of the challenges that comes up though, is if I say SELECT TOP (10) FROM dbo.Products ORDER BY Size, what if there are 5 items with the first Size, and 7 products with the second Size? Which rows get returned when I just ask for 10?

Perhaps what you want is the TOP (10) but then, when you get to the end of the 10, if the next one has the same value you might want to continue on. T-SQL has a way of doing this. We say WITH TIES as shown in the code below:

2019-07-22

About a year ago, I attended a conference called DevOps Days in Newcastle, Australia. I wish I’d liked the conference more. But one of the memorable things from the conference was a keynote by David McRaney.

All the people around me seemed to find David’s keynote interesting. Ironically though, I also heard many of them wondering what on earth it had to do with the conference topics. David provided example after example of how we all suffer from confirmation bias. I enjoyed his keynote.

2019-07-19