The Bit Bucket

Back in 2007, I wrote a book called Building Technical Communities. It was widely distributed at the time, and I’ve always been pleased with the feedback that I received on it.

For some time, I’ve been thinking about updating it. Recently, our MVP and RD lead Shiva has encouraged me to put my ideas somewhere they can be seen, so I’ve decided to add a blog post series that covers what I currently think about this. This is the first article. I’ve love to get your feedback.

2019-11-24

The Usual Situation

In general database terminology, a primary key is one or more columns that can be used to identify a particular row in a table. The key needs to be unique, and it can’t be null. An example would be a CustomerID or CustomerKey in a Customers table.

A foreign key is one or more columns that refer to a key in another table. A common example would be a CustomerID column in an Orders table. Foreign keys can be nullable and are checked when they contain a value.

2019-11-22

There was a poster on a forum recently who was puzzled about disk activity on his system. He had created his database files on non-system drives, yet when he checked the activity on the C drive, it was constantly thrashing. As SQL Server was the only application of any note on the system, he was wondering what on earth was going on.

There are things in the OS that will write to the drive but I think the most likely candidate is that tempdb is still on the OS drive.

2019-11-21

Often when I convert a decimal number to a string in T-SQL, I want the simplest version of the number. I really don’t want any trailing zeroes (i.e. at the end of the number).

In our free SDU Tools for developers and DBAs, we added a function to do just that: TruncateTrailingZeroes.

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

You can use our tools as a set or as a great example of how to write functions like these.

2019-11-20

I’ve mentioned before that Orin Thomas tends to give me many good book recommendations. The Future of Capitalism by Paul Collier is another of Orin’s recommendations. I notice from the cover that it’s also highly recommended by Bill Gates. So it had to be worth a look.

I ended up listening to this via an Audible audio book. It’s quite a long book at around 9 hours 26 minutes.

Overall, I loved this book. I’d have to say though, that it took me quite a while to get into it. I just didn’t find the first chapters all that compelling. But strangely, I then really did get into it.

2019-11-19

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