The Bit Bucket

Prior to SQL Server 2012: IDENTITY Columns

When we needed to automatically number rows in SQL Server prior to SQL Server 2012, one option was to use IDENTITY columns.

(Apologies to purists who think these were the spawn of the devil but real people did use them).

One of the challenges with IDENTITY columns was when you wanted to move data from table to table but needed to retain the same value from the IDENTITY column. To do that, we used SET IDENTITY_INSERT tablename ON; That was fine but an issue that arises, is that if the next IDENTITY value was going to be 245001 and you just inserted a row with 245001, what would happen with you turned IDENTITY_INSERT back off and tried to insert another row.

2018-02-14

I’ve concluded that many software vendors (particularly large ones) don’t understand how much support users of their software provide to each other, and how critical that support is.

The SQL and data communities are a good example of this. When someone has a problem and are wondering how to solve it, they don’t call Microsoft or Google or Oracle (or whichever vendor) first. If they’re lucky, they ask a colleague for help. But most will simply make a Google search (or yes a Bing search) to try to find an answer.

2018-02-13

If you’ve been working with databases for any length of time, you will have come across implementations of Entity-Attribute-Value (EAV) data models (or non-models as some of my friends would call them).

Instead of storing details of an entity as a standard relational table, rows are stored for each attribute.

For example, let’s create a table of people:

When we query it, all is as expected:

And for a long time, this has been how we expect to create tables.

2018-02-12

I spend a lot of time in large organizations that have spent an absolute fortune on highly-available systems, yet when those systems fail over (just as they were designed to do), most of the applications in the building break.

Why?

Because the developers have assumed that nothing ever breaks and have written their code in a far too optimistic manner. Did they do their jobs?

No

Is it possible for their next layer of code to deal with, say, a server disappearing for a few seconds? Of course it is. But it’s not going to happen by accident. It’s even more important in a cloud-based world.

2018-02-09

One of the problems with applications that have highly-configurable user interfaces (UI) is that users can end up configuring them in ways they hadn’t intended, and then don’t know how to get back to where they were.

I remember the first time that I was at a session with a presenter from Microsoft showing the (at the time) new personalization options in ASP.NET. You could build a website and let the user determine how the site should be laid out, to suit themselves.

2018-02-08

One of the endless challenges when working with databases (and not just SQL Server databases) is processing dates and times.

While there are some good date-handling functions in the T-SQL language, there are many more gaps in what’s offered. This month, we’ve filled another one of those gaps.

In Australia, whether staff are religious or not, the Christian Easter is associated with a four-day weekend for most workers except those in retail ie: Friday (Good Friday), Saturday (Holy Saturday), Sunday (Easter Sunday), and Monday (Easter Monday). For many, it looks like this:

2018-02-07

Digital Transformation and Cloud Transformation are phrases that I hear bandied around at nearly every large organization that I currently doing consulting work for.

Yet, in so many cases, I can’t see the organization achieving the changes required. This is for two core reasons:

• The first is that the culture within the organizations is a major hurdle. There just isn’t enough flexibility to think outside the box about alternative ways to work.
• Worse (and probably more concerning), I see these companies taking advice on how to make these transformations from companies who don’t themselves “get it”.

An organization that is cloud-antagonistic internally, and stuck in an endless IT management quagmire, isn’t likely to make a good cloud transformation, and they’re certainly not going to be a successful partner to be able to help you to make a successful cloud migration or to implement a cloud transformation within your company.

2018-02-06

One question that I’m asked all the time when consulting is whether reducing the size of database tables will make queries run faster or not.

The underlying question is typically about whether the client should implement some sort of archiving strategy, to reduce the amount of data in a table by moving older data off into another table.

My answer is that it might help, but if it does, you probably have another issue that would be a better one to solve instead.

2018-02-05

One key decision that you need to take when you decide to include databases in your DevOps processes, is how you will store the data model. What flows from that is how you will do deployment.

Migration-Based Deployment

This is still the most common model today and is a very traditional way to work with databases during development.

At some point, you create an initial database, and after that, you keep every script that’s needed to bring the schema up to the current point. Those scripts will often include data fixes or new values for reference tables, as well as the DDL required for the schema changes.

2018-02-02

SQL Server Management Studio (SSMS) is a great tool and it has lots of helpful menu items and toolbar items. Unfortunately, all these items take up screen real estate.

You can see that the default screen layout could be considered a bit cluttered if you really just want to focus on the particular query that you’re working on.

A keyboard shortcut can help here. Alt-Shift-Enter toggles full screen mode in SSMS.

2018-02-01