Of Hedgehogs and Database Design

One of the finest songs of the sixties had the following lines …

Sitting one day by myself,
And I'm thinking, "What could be wrong?"
When this funny little Hedgehog comes running up to me,
And it starts up to sing me this song.
"Oh, you know all the words, and you sung all the notes,
But you never quite learned the song", she sang.
"I can tell by the sadness in your eyes,
That you never quite learned the song".

(Mike Heron: The Hedgehog's Song)

I once spent a fascinating couple of years auditing and inspecting the databases of Her Majesty's Government. I often hummed the Hedgehog Song to myself as I stared at the database scripts and listened to DBAs in charge of the databases that affected the lives of millions of people.

I've seen many databases; a few great ones, some good'uns and many gor'blimey ones. After a while, it was easy to detect the difference. Mike Heron's sad song about love applies just as appropriately to database design. There is a world of difference in knowing the words of relational theory, and being able to hum it, but the practice of doing it properly is another stage.

The well-constructed relational databases just seemed to work. They didn't require endless tinkering and maintenance. Tables were well normalised, narrow, appropriate, and ingenious. Constraints and keys abounded. The data model fitted the business like a glove. The SQL was simple because it just needed to declare what result was required, no hints, no fancy stuff, and only rarely extra indexes. The effort was focused on maintaining an immaculate, normalized representation of the organization's data. On the rare occasion that a query was slow, the dev teams assumed that the fault lay in the data model, and they'd fix it.

There was one such database I knew that was responsible for the clearing of cheques for a major retail bank. It processed millions of business transactions a day. The team, a handful of people that created and maintained it, were nearing retirement so two major IT projects, involving hundreds of bright young developers, aimed to replace the system. Both failed. The original team became used to being called away from tending their roses to maintain the system that kept the organization functioning.

As always, it isn't so much the algorithms but the data structures that determine how effective a relational database will be. If your database consists of a few poorly designed "Godzilla" tables, it is much harder to re-engineer them without the lights flickering, raised voices, and the sounds of approaching sirens. How can one justify iteratively developing a database model when it is easier to keep things running by hints, heuristics, and hardware? My experience tells me that disguising the problem in a relational database always ends badly, because, by doing so, it escalates complexity, and that is a scary trend for any IT system. Instead, narrow, well-designed relational tables will support nimble, iterative development, but it requires knowing how to sing the song.

Phil Factor

Join the debate, and respond to the editorial on the forums

Starting out a new database development with source control is relatively easy. To introduce source control into an existing database application can be more challenging. Phil Factor explains some of the fundamental steps.

In Redgate Change Control v3.0, you can now commit your database changes to your local Git repository and collaborate with your team by pushing and pulling changes from the remote Git repository. If you’re using branches, you can also create and switch Git branches from within Redgate Change Control.

Azure Key Vault is a service for storing securely certificates, credentials, connection strings, and more. In this article, Rishit Mishra walks through how to use the service to secure a connection string for an application.

A custom solution that lets you check the consistency of all the databases under your care, save only the end result (without that much overhead) with a few additional important values, while having to modify only a couple of things that are very specific to your use case.

We can easily migrate jobs and similar objects from one SQL Server instance to another. For that, we need a backup and restore process of msdb. Similarly, we can recover these objects on an SQL Server instance if lost for some reason.

Using Upgrade Advisor, you can determine whether your existing databases are suitable for upgrade to SQL Server 2016 or any other higher version.

Usually, we build a shadow table with the new schema, create triggers to keep both copies in sync, and then batch/backfill at that team's own pace until they are ready to swap in the copy as the real deal.. However, when brute force is valued over surgical precision, and especially if you can take a slice of downtime, there can be value in the simpler approach for certain table shapes.

On the surface sp_helpdb looks great, and for the most part it is. It returns some great information with one line of code. However, when looking at the actual execution plan, this is where it starts to get ugly.

Garry Bargsley tackles the final frontier of a tidy SQL Server system: standardized SQL Server Error Log configurations

While connections and sessions don’t take much memory compared to what the actual SSAS data does, they still take some memory that can be valuable to free up if a server is under resource pressure.

One of the only things platform as a service databases like Azure SQL Database do not do for you is actively manage column and index statistics.

Following on from my posts about using Secret Management Good bye Import-CliXml and running programmes as a different user, I have another use case. After creating Azure SQL Databases...

Redgate’s Events Marketing Manager, Annabel Bradford, describes how to get the most out of your next online event.

Discover how Redgate's latest database DevOps innovations empower developers to code in the IDEs of their choice, version control database changes in plain SQL, and easily validate their changes against a masked copy of production as soon as they make the change.

On Wednesday, August 26, join world-class speakers including Donovan Brown, Abel Wang, Kendra Little and Hamish Watson for a full day of educational content. There's also the opportunity to network with your peers across the global community. So, wherever you are in the world, join us live.

All DBAs understand the risks of having logins with a high privilege level, so the objective of this module is to give you a quick glance across all the instances under your support, so that you can act accordingly.

Even though Shane O'Neill can’t do maths before coffee, he can at least write PowerShell.

Fully automated hands-off PowerShell scripts are useful for any DBA or System Administrator, but what if you need to get input from the user, or maybe you want to implement a menu system?

PowerShell is a basic skill any administrator working in Windows or Azure should know. After writing his first article about PowerShell parameters, Greg Moore realized that there is even more to talk about. This article covers using pipelined parameters and more.

Nagdev Amruthnath argues that while Benford’s Law is widely used to detect accounting fraud, manipulations in income tax filing, and so on, there are still many more cases where it could be used to our advantage, like identifying bot accounts on Twitter and Facebook or identifying modified images or music.

Industry misconceptions nowithstanding, a database relation is by definition in both first normal form (1NF) and fifth normal form (5NF), otherwise it is not a relation and all bets are off.

Single-column clustered indexes make a lot of sense. Single column nonclustered indexes often make less sense. It’s like the difference between a chef knife and a Swiss Army knife. You want one to be really good at one specific task, and another to be pretty useful to a bunch of tasks.

Clustered indexes are essential, but of limited overall value for searching for data. As soon as we want to search by any other columns without searching for a specific Id, we need some non-clustered indexes to make it easier for SQL Server to find that data.

Ajay Dwivedi tries to devise a more versatile script for returning information about blocking queries.

Recording ongoing performance information is vital as it provide a history of the system state in case issues arise, and a baseline of your system’s resource consumption.

What if you need to know not just what’s happening while you’re running a script to sample the database, but also everything that has happened since the last time that you’ve run the script? Extended Event sessions with a ring buffer target give you all this, plus the ability to minimize your observer overhead.

Phil Factor sets out with the modest aim of giving you enough of an introduction to waits to better understand the wait information you get from a SQL Server monitoring tool like SQL Monitor, and the rather overwhelming amount of information available in the underlying DMVs and Extended Events.

This article describes how to create Key Performance Indicators (KPI) in a Power BI Desktop data model by using Tabular Editor.

Power BI Desktop recently added some new functions to make it easier to work with geographic and geometric data. Chris Webb explains the basics of how the functions work.

The visual customization feature allows any Power BI user that is consuming the report to customize they way it looks, without affecting the centrally managed report.

Your data is rarely going to be in the right format for analytic reporting. It may need to be consolidated into related fact and dimension tables, summarized, grouped or just cleaned-up before tables can be imported into a data model for reporting.

The standard data warehouse design from Kimball with facts and dimensions has been around for almost 25 years. In this article, Vince Iacoboni describes another way to design slowly changing dimensions.

You would like to extend the capability of a scalar function to be apply to apply regex expressions in your SQL Server database. Advantages to implementing this in a scalar function can help utilize regex search patterns to identify, clean and parse existing SQL Server data.

Do table variables and variables respect transactions? If I set the value of a variable during a transaction, and I roll it back, what happens?

When you run a query, how does SQL Server estimate the number of rows? Brent Ozar explains in this half-hour video demo, using playing cards, then shows the same issues in SQL Server Management Studio.

Could it be that conventional index design wisdom is based on a faulty algorithm?

For SQL Server queries that require additional memory, grants are derived for serial plans. If a parallel plan is explored and chosen, memory will be divided evenly among threads.

Louis Davidson explains a "weird occurrence" that can happen when your trigger validation code queries another table in the default isolation level. You expect that your data is protected until your transaction ends, but it may not be.

Hugo Kornelis explains when you might encounter the Row Count Spool operator in your SQL Server query execution plans, what is does and how it works.

We recently performed a DDL operation against a SQL Server table – simply increasing the size of a varchar column – which should have been instantaneous. Instead, we killed it after observing 20 minutes of HARD_SYNC_COMMIT waits and a blocked replication log reader. Could this issue have been avoided? What went wrong?

Uwe Ricken continues his series on heaps. This time he demonstrates a common scenario where the query against a heap is faster than a clustered index.

Containers have already transformed the way application development works, but adoption has been slower for databases. Finally, the revolution is beginning. In this post, Kendra Little shares the two ways in which containers will dramatically change the way teams develop and deploy database changes.