The Ironic DBA Files—Episode 4: A New Primary Key

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

Last time we took a look at some nomenclature related to SQL Server and touched on the basic differences between clustered and nonclustered indexes.

I’ll be honest here in this post—I’m worn out—but it’s the good kind of tiredness. Those of you who have been DBAs for a year or more probably don’t realize just how much knowledge you’ve absorbed and put into practice since you started. And those who are Senior DBAs…well…your knowledge can often look like magic to the uninitiated apprentice. (Insert Gandalf image here.)

I’m deep enough into the weeds that I’m finding it a bit hard to drill down to something specific to write about this week, but I’m going to give it my best try. Apologies in advance if this seems like a bit of a ramble.

The Keys to the Kingdom

Picking up a bit where I left off last week, let’s talk about Primary Keys.

Remember, a table is made up of columns and rows. Data in a database table is stored in the “cells,” or fields. Typically, tables in SQL Server have a column or set of columns that contain values to uniquely identify each row. This column/column set is known as the Primary Key.

A table can only have ONE Primary Key, though—as mentioned above—it is possible that a Primary Key can consist of either single or multiple columns.

How do you know if your table has a Primary Key? Well, if you created the table yourself and haven’t built a clustered index on that table yet, your table probably doesn’t have a Primary Key unless you created a primary key at the time of table creation.

Here’s a quick bit of code that shows how you might create a new table with a primary key constraint.

-- Create table with primary key constraint
CREATE TABLE Projects
(
    id INT NOT NULL,
    name VARCHAR(255),
    start DATE NOT NULL,
    end DATE NOT NULL,
    CONSTRAINT PK_Projects_name PRIMARY KEY (name)
);

The above snippet of T-SQL creates a table named “Projects” with four columns: id, name, start, and end. The project_name column is defined in the code snippet as the table’s primary key constraint.

Now, what you need to realize at this point is that as soon as a Primary Key is created on a table, that table is now a clustered index. The Primary Key is now also a clustered index key.

Note: The above column names are really not all that great, but are kept simple for illustrative purposes to my fellow apprentices.

I Need a Different Clustered Index Key

What you may or may not recognize right away is that a table’s Primary Key might not be the best value to build a clustered index upon. Hopefully you’ll have been able to put some thought into the process of building a new database’s architecture and have a chance to set keys up as best you can from the start.

Even if you get that opportunity, over time you may discover that user’s queries would be better served with a different indexing structure—which leads you to figure out how to drop a Primary Key in order to build a new clustered index.

Before we move on, let’s take a moment to consider what makes a great clustered index key. [Hat tip to the highly recommended Pluralsight Course SQL Server: Indexing for Performance by Kimberly L. Tripp. (b|t)]

Characteristics of a good clustered index key:

Unique—Value is not duplicated elsewhere.
Static—Value will not change.
Narrow—Value is as specific as possible.
Non-nullable—Value cannot be NULL.
Fixed width—Value of the key has been assigned a fixed width.
Ever-increasing—Value increases over time.

So, what happens if sometime in the future you determine the current Primary Key isn’t the best choice for your clustered index? You have to perform two T-SQL commands. You have to drop the primary key constraint and then build a new clustered index.

--Drop constraint
ALTER TABLE Projects
DROP CONSTRAINT PK_Projects_name
GO

-- Create clustered index
CREATE CLUSTERED INDEX IX_Projects_id
     ON dbo.Projects (id)
GO

HT: Pinal Dave (b|t)

Go ahead and test all the above code snippets out on your test instance. Wait, you don’t have a test instance?

Guess what? You can set up a inexpensive test instance by setting up an Azure account and creating a VM. Just be sure to pay attention to how you structure your resources and how long you keep it running each day. There’s no need to go crazy and create a VM that’s an uber-computer and leave it running 24/7. That’s a quick way to go bankrupt.

That’s all for this week. Join me next time for The Ironic DBA Files—Episode 5: The Maintenance Plan Strikes Back.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.