The Bit Bucket

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

I wish query tuning was easy. Today, it’s not. One day machines will do it better than us, but today, doing it well is still somewhat of an artistic pursuit. You can teach most people the basics, but it takes a long time to get a really good “feel” for what needs to be done. However, something that I seem to differ with a lot of people on, is about how many indexes is too many?

2019-07-18

Most SQL Server tables should have a clustered index. There are some exceptions to this but it’s a pretty general rule, and if in doubt, you should follow it. (Note that this is not the same as having a primary key).

I regularly come across tables without clustered indexes for all the wrong reasons. So, in our free SDU Tools for developers and DBAs, we added a tool that can look for user tables that don’t have a clustered index. No surprise, it’s called ListUserHeapTables because a table without a clustered index is a heap.

2019-07-17

I continued to be stunned at how banks don’t get security.

Had a call just now from a sales guy from the my bank. It’s annoying enough that they call at night, but I really don’t like it when they want to confirm who you are before they can talk to you.

Told him, Sorry, can’t do. Have no idea who you are.

Why would I tell personal details to some guy who just calls out of the blue, claiming to be from my bank?

2019-07-16

The TOP operator is used to restrict the number of rows being returned. If you’re familiar with other database engines, you might have used a LIMIT operator. TOP is a bit like that.

You can see that in the first query shown below:

If you look at the bottom query, you can see that it can also be used to limit the rows to a percentage instead of a number of rows.

2019-07-15

I’ve mentioned previously that I’ve come across Graeme Simsion previously in his role as a well-known data modeller, based in Melbourne here in Australia. I’ve recorded a podcast with him many years ago, on my SDU Podcast series. So perhaps I have a slight bias towards him as an author.

I was so excited to see the endless well-deserved congratulations he’s received for his initial Rosie Project book. I thoroughly enjoyed that book.

2019-07-12

I noticed a post the other day by one of my friends and fellow MVP Aaron Bertrand where he mentioned that he used to scoff at the TRIM() function, at least until he read the documentation.

Almost as long as I’ve worked with SQL Server (since 1992), I’ve heard people complaining about the lack of a TRIM() function. No-one could understand why we had to keep writing both LTRIM() and RTRIM() to get the required result. So it wasn’t surprising that we were all happy in SQL Server 2017 when TRIM() finally got added.

2019-07-11