The Bit Bucket

I often need to remove spaces from the front or back (or both) of strings. For a long time, the two functions we had for that were LTRIM (for left trim) and RTRIM (for right trim).  You can see them in the first two SELECT statements here:

For as long as I’ve used SQL Server though (also a long time), people have been asking for a TRIM function. Most developers were tired of endlessly writing LTRIM(RTRIM()) to do that. In SQL Server 2017 though, we did finally get a TRIM function. It trims both the left and right hand sides of a string.

2019-11-18

Snowflake has a richer set of options for how tables are created, than we have in SQL Server.

CREATE TABLE Variants

As well as a CREATE TABLE statement as you would already be familiar with in SQL Server, Snowflake offers these variants:

CREATE TABLE tablename AS SELECT

This is basically similar to a SELECT INTO in SQL Server. It executes the SELECT query and creates a table from the results (including the data).

2019-11-15

I was answering a question on a forum the other day, and the person asking the question was puzzled about how to send an email to an operator (i.e. msdb operator) rather than to just any other email address.

The reason he wanted to do that was that he wanted to use the Agent operator system to define a name. He didn’t want to hard-code a recipient name in his code. By having it as an operator, it could be changed later, independent of the code.

2019-11-14

We’ve got a lot of very useful string-related functions in SDU Tools. This is another one. At times, there’s a need to remove excess spaces between words in strings. In our free SDU Tools for developers and DBAs, we added a function to do just that: SingleSpaceWords.

The function just takes one parameter: the string to be processed.

Find out more

You can see it in action in the main image above, and in the video here:

2019-11-13

I spend quite a bit of each year working with spatial data (i.e. mostly mapping data but sometimes other data). One of the constant challenges is where to find the spatial data. Another challenge is where to find services that can help you to work with the data.

For Australian data, if you haven’t considered Mappify.io, I think you should take a look.

You need to create an account before you start but it’s free to create an account to try it out. Then there are a number of services.

2019-11-12

Joining together strings is called concatenating strings. In the first example here you can see SELECT hello, plus NULL, plus there. Now the trick with this, though, is that NULL is a bit of a challenge.

Note that the outcome of that first select statement is NULL is just NULL. As soon as you concatenate something with NULL, you would end up with NULL as the overall answer.

2019-11-11

If you want to learn a lot about how Snowflake works internally, it’s worth spending some time reading the ACM whitepaper that described the service.

It describes the overall structure of the service, and then describes how data is stored, followed by a description of how the encryption of the data within the service is accomplished. (I’ll talk more about the management of encryption keys in a later blog post).

2019-11-08

Another question that I recently saw on Stack Overflow was asking how you SELECT a bunch of transactions back past a cutoff date, add them up, INSERT a roll-up row, and then delete the old ones. The concern was with how you make sure the ones you are deleting are the same rows that you are rolling up (or totalling). Someone else might UPDATE or INSERT a row at the same time.

2019-11-07

More and more users of SDU Tools are adding the deployment of the tools into their standard deployment pipelines, and into their standard operating environments (SOEs).

One common but simple request that we’ve had is that they need to be able to check which version of SDU Tools is currently installed. So we added a function to do that.

It doesn’t take any parameters but just returns the version number as a string.

2019-11-06

In a recent consulting project, I needed to plot analytic values over postcodes. I couldn’t just do it with the built-in maps control, mostly because the system that we’re developing on doesn’t have Internet connectivity. I was keen to upload the mapping data directly into the report, and the Shape Map control (while still in preview) seemed to fit what I needed exactly.

However, when you load custom maps into the control, they need to be in TopoJSON format. Now TopoJSON is a specific extension over GeoJSON which has been pretty common for a while. I tried just using a GeoJSON file with the control, to no luck.

2019-11-05