The Bit Bucket

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

Recently, I posted a link to show how to turn on ANSI_NULLS for  a table. It’s not normally easy to change that but we added a procedure to our free SDU Tools to make it easy.

But one of the comments I received was a question basically saying “OK, you’ve shown how to change it but you haven’t mentioned why it matters in the first place”.

Fair enough. So that’s the topic of today’s post.

2018-03-19

I had a previous life as a developer and ran a software-development house. Even then, I was very focussed on data. I don’t think I’ve ever had a BD (before data) period. I see almost everything I’ve ever worked on in data-related terms, so perhaps it’s the time before I focussed on data.

But what this does mean is that whenever I get together with other data-related people, I’m one of the people who is asking why things that are taken for granted in the developer community, aren’t present in SQL Server and its T-SQL language.

2018-03-16

I’ve mentioned in an earlier article about scripting multiple objects at once, how useful the Object Explorer Details window is, and how little understood it is.

Another useful option in it, is that the displayed columns can be changed. In particular, you can add columns that would be useful. Let’s look at an example.

In Object Explorer, I’ve expanded the WideWorldImporters database and clicked on the word Tables:

Next, I hit the F7 key, and the Object Explorer Details pane opens showing this:

2018-03-15

When you create script out a table in SQL Server using SQL Server Management Studio, you’ll notice that it scripts more than just the table itself. Before the table, it scripts the values for ANSI_NULLS and QUOTED_IDENTIFIER.

A common problem that I see is that someone highlights the CREATE TABLE statement and runs it, without highlighting the SET options above it. That has the chance of leading to the wrong values. I don’t run into big issues with QUOTED_IDENTIFIER but I certainly run into issues with ANSI_NULLS.

2018-03-14

I spend a lot of time with developers and with ISVs (Independent Software Vendors). Often, I come across really weird table designs. A common problem is the creation of tables that hold many different types of objects.

If I look at one of your tables, and ask you what it holds, your answer shouldn’t start with “It depends”.

A worse design is one where the same discussion happens about columns. If I ask you what is held in the ObjectID column, and you say “that depends upon what’s in the ObjectType column”, you’d better be building some sort of utility, not a relational database to support an application. There are so many downsides to this type of design but that’s a topic for another day.

2018-03-12

I’ve recently been making use of SQL Clone from Redgate, at various client sites. I have to say that I really like it.

The basic concept is that you take an existing SQL Server database, you create an “image” from it, then you create database clones from that image.

Under the covers, it’s basically a differencing technology. The initial image is like an initial set of database files, and each clone is then files that are differenced from that. That makes it very easy to spin up clones, and to break them down again. While this isn’t really suitable for much in the way of performance or load testing, as everyone is sharing the same base, it’s perfect for general development.

2018-03-09