The Bit Bucket

If you’ve hung around the SQL Server community for any length of time, you’ll have heard the endless jibes about the AutoShrink option for databases. Using it is almost always a bad idea. In fact, shrinking databases is typically not a great idea but the AutoShrink option is considered universally bad and there are calls for it to be removed as an option.

We often joke that “auto shrink” should be renamed to “auto fragment my filesystem”, because that’s typically the outcome.

2018-04-02

I’ve spent a lot of time lately working with automated testing systems.

Unit tests are great for checking blocks of code, etc. but integration tests and functionality tests are critical to projects. An area that is still tricky though is UI testing. There are some really good tools for this.

Selenium has been very popular and works well if you access it programmatically. There are good framework libraries for .NET and we’ve been using those. Selenium also had a pretty good IDE that could be used for recording but it’s pretty much adrift now as it was based on an older version of Firefox and doesn’t work on the latest versions. You could install it on an older version for testing but those versions aren’t safe to use so the recommendation is to avoid it. Working with it programmatically is not that hard though.

2018-03-30

If you are working with databases with large numbers of objects, the contents of Object Explorer in SQL Server Management Studio can start to become a bit overwhelming. I have to admit that I don’t understand why it doesn’t offer an option to group by schema. That would be helpful.

But you can at least filter by things like schema, when you need to work with a specific set of objects. You’ll notice that if you click on the database name, that the filter in the toolbar is grayed out, but if you click on a node below that like Tables, you can click on the toolbar filter icon. You can also right-click the node and choose to filter:

2018-03-29

Working with dates and times in database systems and programming languages has always been “interesting”, at least interesting in the sense that the old Chinese curse meant when it said “may you live in interesting times”.

One of the curious variations though is the use of Julian day numbers. It’s a count of the number of days since the beginning of the Julian period: https://en.wikipedia.org/wiki/Julian_day

SQL Server doesn’t currently have a built-in function for converting to or from these so we added functions to our free SDU Tools for developers and DBAs. You can use the functions directly from our tool kit, or use them as examples of how to write these functions.

2018-03-28

The CREATE INDEX statement is used to do exactly what its name says, it creates an index. But when you say CREATE UNIQUE INDEX, you are doing more than that; you are enforcing a business rule that involves uniqueness.

I have a simple rule on this. Wherever possible business rules like uniqueness, check values, etc. should be part of the design of the table, and not enforced in an external object like an index.

2018-03-27

There are a number of words related to SQL Server that are overloaded. One of those is “mirroring”.

Mirroring backups has nothing to do with other concepts like database mirroring.

Mirroring backups allow SQL Server to send the same backup to more than one location at the same time. The Books Online page for the BACKUP command describes the clause this way:

“MIRROR TO <backup_device> [ ,…n ] Specifies a set of up to three secondary backup devices, each of which mirrors the backups devices specified in the TO clause. The MIRROR TO clause must specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three.”

2018-03-26

I was asked a curious question the other day: “Are all changes made to production systems actually deployments?”

I wish the answer was yes, but unfortunately patches do get applied directly to production systems without going through standard deployment processes.

Everyone understands the scenario. Code has been deployed using the normal processes, then someone has something that simply must be fixed urgently in the productions systems. At that point, you have two choices:

2018-03-23

I started working with SQL Server in 1992, but all through the 1980’s and 1990’s, I was also working with Progress 4GL. I thought it was the best of the character-based 4GLs but unfortunately, they did a poor job of migrating to Windows and we decided to stop using the product.

One thing that I used to love with Progress though is that the metadata for each column in the database was much richer than what is present in SQL Server. In fact, Microsoft Access was probably closer to it in that regard. It’s something I really missed when moving to SQL Server. In Progress, when I defined a column, I could also define things like:

2018-03-22

Some of the simpler tools that we’ve added to our free SDU Tools collection over the last year or so, have turned out to be quite useful.

A good example of this is CountWords.

This function takes a T-SQL string, removes the punctuation, excess whitespace, etc. and then counts the number of words contained. There are a few things that can still trick it (avoiding that would need very detailed langauge parsing) but it’s quite good.

2018-03-21

One of the changes that has happened with Windows in recent years is the concept of forced updates. Basically, you’re going to get updates from now on, like it or not. Generally that’s a good thing. You can delay them for a little while but not for that long. In the Advanced options update Updates, you can see this:

So you aren’t going to delay them for that long.

2018-03-20