The Bit Bucket

One of the endless challenges when working with databases (and not just SQL Server databases) is processing dates and times.

While there are some good date-handling functions in the T-SQL language, there are many more gaps in what’s offered. This month, we’ve filled another one of those gaps.

In Australia, whether staff are religious or not, the Christian Easter is associated with a four-day weekend for most workers except those in retail ie: Friday (Good Friday), Saturday (Holy Saturday), Sunday (Easter Sunday), and Monday (Easter Monday). For many, it looks like this:

2018-02-07

Digital Transformation and Cloud Transformation are phrases that I hear bandied around at nearly every large organization that I currently doing consulting work for.

Yet, in so many cases, I can’t see the organization achieving the changes required. This is for two core reasons:

• The first is that the culture within the organizations is a major hurdle. There just isn’t enough flexibility to think outside the box about alternative ways to work.
• Worse (and probably more concerning), I see these companies taking advice on how to make these transformations from companies who don’t themselves “get it”.

An organization that is cloud-antagonistic internally, and stuck in an endless IT management quagmire, isn’t likely to make a good cloud transformation, and they’re certainly not going to be a successful partner to be able to help you to make a successful cloud migration or to implement a cloud transformation within your company.

2018-02-06

One question that I’m asked all the time when consulting is whether reducing the size of database tables will make queries run faster or not.

The underlying question is typically about whether the client should implement some sort of archiving strategy, to reduce the amount of data in a table by moving older data off into another table.

My answer is that it might help, but if it does, you probably have another issue that would be a better one to solve instead.

2018-02-05

One key decision that you need to take when you decide to include databases in your DevOps processes, is how you will store the data model. What flows from that is how you will do deployment.

Migration-Based Deployment

This is still the most common model today and is a very traditional way to work with databases during development.

At some point, you create an initial database, and after that, you keep every script that’s needed to bring the schema up to the current point. Those scripts will often include data fixes or new values for reference tables, as well as the DDL required for the schema changes.

2018-02-02

SQL Server Management Studio (SSMS) is a great tool and it has lots of helpful menu items and toolbar items. Unfortunately, all these items take up screen real estate.

You can see that the default screen layout could be considered a bit cluttered if you really just want to focus on the particular query that you’re working on.

A keyboard shortcut can help here. Alt-Shift-Enter toggles full screen mode in SSMS.

2018-02-01

Percent Encoding (also known as URL Encoding) is a technique for encoding the values in strings that are not able to be directly represented, according to the rules for the target location. For example, not all characters are permitted in URLs.

Percent encoding a T-SQL string is also useful when working with XML. For example, one common method for splitting strings is based on the use of XML PATH but if you try to use those types of string splitters with strings containing, say, percent signs, you’ll find they just don’t work.

2018-01-31

When many SQL Server customers are asked why they haven’t kept up to date with either SQL Server versions, or more importantly, patches to SQL Server, the answers usually boil down to two reasons:

• They are just slack
• Their vendors won’t support the later version or patch level

Many SQL Server application vendors don’t keep up to date with testing of their applications on released versions or patches for SQL Server.

2018-01-30

I spend a lot of time reviewing SQL Server T-SQL code from developers. There are quite a few issues that I see regularly, and one of these relates to UNION.

In SQL Server, a UNION statement is actually a UNION DISTINCT (but we’re not allowed to put the word DISTINCT in the syntax). The alternative is a UNION ALL. Often a UNION ALL is what’s needed, and using a UNION leads to unnecessary performance problems.

2018-01-29

I keep doing work at sites where none of the database code is stored in version control (source control) systems. I keep wondering why that is.

At a recent site, all the source code was in individual files just sitting in a single folder. That’s just not sensible.

I’m left wondering why it is that almost every team that I see working with higher-level languages just assumes that some form of source control would be used, yet it’s almost the opposite when I’m working with data teams.

2018-01-26

It’s likely that everyone who uses SQL Server Management Studio (SSMS) knows how to tell how long a query ran for. You can see it in the bottom right of the status bar when a query finishes.

But one question that often comes up with a long-running query is when did my query finish?

That’s not in the status bar and many people don’t seem to be aware that you can find it out.

2018-01-25