The Bit Bucket

When you first start working with SQL Server, you’re often working with smaller databases and every backup seems to happen quickly. SQL Server is fast at creating backups.

When you get to larger databases, backups start to take a while. Fortunately, backup is one of the commands that sets a percentage complete value that can be seen in the sys.dm_exec_requests system view.

And when the databases get even larger, the question quickly becomes:

2017-12-27

I work with a lot of SQL Server databases that are poorly normalized. One of my pet dislikes is the column to rule them all.

Here are simple tests:

If I ask you what’s stored in a column and you can’t tell me a single answer , then you’ve got a problem.

If you need to refer to another column to work out what’s in the first column, then you’ve got a problem.

2017-12-26

Over the years, I’ve had a surprising number of questions on how to right-align a set of numbers using T-SQL.

The first concept that people seem to miss is that numbers aren’t strings. So there’s no concept of actually aligning a number, it’s only a string representation of a number that can be right-aligned or left-aligned. Or how it’s displayed in a client application.

But if you really want to create a string that has right-aligned numbers, then left padding of the number is what you want.

2017-12-22

In T-SQL, a script is a set of one or more batches.

For example, if we have the following script and click Execute, it looks like all the commands were sent to the server and executed all at once:

But that isn’t what happened.

What did happen is that SQL Server Management Studio (SSMS) found the word GO and broke the script into a series of batches. In this case, there were three batches. First, it sent the commands shown here as Batch 1 to the server, waited for them to execute, then sent Batch 2, waited for it to execute, then sent Batch 3, and waited for it to execute.

2017-12-21

I’ve written before about how important it is to find code smells.

One of these relates to foreign keys. I’ve argued in previous posts about the importance of foreign keys but if you do have them in place, you need to index them.

When SQL Server creates a primary key, it creates an index under the covers to support the primary key. The index has the same name as the key. (And is one of the reasons why you should name your primary keys and not let the system do it for you). A primary key needs to be both unique and NOT NULL. So SQL Server creates an index so it can quickly check if a value already exists.

2017-12-20

Intrusions into computer systems are happening all the time now. We need to address this issue as an industry, but it’s important to understand that the way we design databases plays a big role in the impacts that occur during intrusions.

If you don’t accept that you could have an intrusion, you are living in La La Land. (See https://en.wikipedia.org/wiki/Fantasy_prone_personality )

A bug in any one of the frameworks that you use, the code that you write, the protocols that you use, the operating system or hosting services that you use can potentially expose you to an intrusion.

2017-12-19

There were a number of new T-SQL functions introduced in SQL Server 2012. As I’ve mentioned before, I get excited when there are new T-SQL functions.

Some, I’m not so excited about. EOMONTH was in that category, not because of the functionality, but because of the name (wish it was ENDOFMONTH), and lack of symmetry (lack of a STARTOFMONTH or BEGINNINGOFMONTH).

One that I thought was curious was CONCAT. I thought “why on earth do we need a function to concatenate strings. I can already do that. But when I got into using it, I realized how wonderful it was.

2017-12-18

I’ve often had requests for code to convert strings to ProperCase and I described that in a recent blog post, but another common request is for either PascalCase or camelCase.

In PascalCase, all spaces between words are removed, the first letter of each word is capitalized, and the other letters in each word are lower case.

You can see it in this example:

camelCase is similar except that the first letter of the first word is not capitalized. The first letter of subsequent words are capitalized:

2017-12-15

SQL Server databases have a reputation for being hard to test, or at least hard to test appropriately.

For good testing, and particularly for unit tests, you really want the following:

• Database in a known state before each test
• Database containing large amounts of (preferably masked) data (production-sized)
• Quick restore after each test before the next test

For most databases, this is hard to achieve. The restore after each test means that a normal database restore can’t be used. What I often see instead, is people using transactions to try to achieve this i.e. the process becomes:

2017-12-14

I’ve mentioned that I work on a lot of financial systems. Masking and trimming the production databases so they can be used in development is important, yet I see many issues with masking.

A few months back, I was working on a SQL Server database from a large financial client, and the database was supposed to have been masked. However, what they missed was that the XML metadata associated with images stored in the database still had the actual client’s details. Any developer in the organization could have retrieved private details of all the members of the fund.

2017-12-13