The Bit Bucket

In SQL Server, most tables benefit from having a clustering key i.e. the column or columns that the table is logically sorted by. (Note: much old training material and documentation used to say “physical order” and that’s never been true). To do that, SQL Server stores the data within the pages in a sorted way, and maintains a doubly-linked logical chain of pages. In addition, it maintains pointers to the data by creating an index on it.

2019-11-29

A forum poster was asking how to make a SQL Server column NOT NULL, but not apply it to old data. But when you add NOT NULL, there’s no option to say “Just new data”. However, you can work around it to achieve the same goal.

When you add a constraint to SQL Server, you can say WITH NOCHECK. That says to not check existing data. You can add a constraint to ensure a value isn’t NULL.

2019-11-28

When SQL Server requests storage space from the operating system, the default action is for the OS to write zeroes over all the space before it’s presented to SQL Server to use. This is to ensure that the SQL Server process cannot read the data that was previously on that section of the storage (drive).

This can cause significant delays in at least two common situations:

• A file needs to grow
• Space needs to be preallocated during a database restore.

If this security issue isn’t a concern for you (if you have a dedicated SQL Server system, or you have a corporate SAN then it’s unlikely to be), then SQL Server has an option called Instant File Initialization that avoids all the writing of zeroes.

2019-11-27

If you need to convert strings to all upper case or all lower case, you can use the UPPER and LOWER functions in T-SQL as shown below:

These are simple functions and it doesn’t matter what those strings were to start with, it will just do the case conversion.

It’s also worth noting that if you want to make your SQL query text change to upper or lower case in SQL Server Management Studio, there’s a shortcut for that too. If you want to just quickly convert a string to all uppercase or all lower case, just highlight the string and hit control shift U for upper or control shift L for lower.

2019-11-25

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