• Adam Thurgar

Spare columns


I was asked to look at a logical database design just for a sanity check and make sure it all made sense.

It was a good design with primary keys and foreign keys, indexing suggestions (both clustered and non-clustered), constraints and defaults. None of the tables had an extremely large amount of columns and those columns were not excessive in size. I made some suggestions about datatypes, using bigint instead of int, decreasing varchar sizes etc.

The only question that I had was that some of the volatile tables had spare columns in them, usually two. One an int and the other a varchar (50). I asked the designer what was the rationale behind these columns and they said it was to avoid the overhead of potentially having to add a new column later on. Obviously the designer had encountered this issue in the past and was being proactive in their design, but there was an overhead and cost involved. Bad design - No, just a bit unusual.


2 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t