Lengthen a Primary Key–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw a post recently where someone needed to increase the size of a PK and was getting a table rebuild message in SSMS. This is short post to show that isn’t required.

First, let’s create a table and give it some data. Note that the PK is set to a specific size.

CREATE TABLE dbo.Document
(DocumentKey NVARCHAR(5) NOT NULL CONSTRAINT DocumentPK PRIMARY KEY
, DocumentName NVARCHAR(200)
, DocumentDate DATETIME2
)
GO
INSERT dbo.Document
     (
         DocumentKey
       , DocumentName
       , DocumentDate
     )
VALUES
     (N'ABC23', N'Something very interesting', '2019-01-02'),
     (N'QNI43', N'An adventure you admire', '2019-02-02'),
     (N'MNT33', N'Magnets describing life', '2019-03-04'),
     (N'DEF25', N'Time for nothing', '2019-03-12'),
     (N'HIJ54', N'Dreams of the dark', '2019-04-17')
GO
SELECT top 10
  *
  FROM dbo.Document AS d
GO

If I try to insert data that’s larger, I’ll get this message:

2019-04-16 08_58_43-SQLQuery10.sql - Plato_SQL2017.sandbox (PLATO_Steve (53))_ - Microsoft SQL Serve

In SQL 2019, I’ll get a better error, but for now, this shows me a limitation of my key.

Now I’ll increase the size of the key. I use the ALTER TABLE … ALTER COLUMN statement.

ALTER TABLE dbo.Document ALTER COLUMN DocumentKey NVARCHAR(7) NOT NULL
GO

Now, I’ll run my failed insert again:

2019-04-16 09_00_00-SQLQuery10.sql - Plato_SQL2017.sandbox (PLATO_Steve (53))_ - Microsoft SQL Serve

As you can see, I can increase the size of the PK without rebuilding the table. Making it smaller is a post for another day.

SQLNewBlogger

This was a quick repro I set up to answer the question for myself and others. I thought I could do this and spent five minutes proving it.

The longest part of this post was the test data. You could do the same thing, maybe showing how this relates to a child table as well. In fact, start today and you might beat me to creating that post.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

1 Response to Lengthen a Primary Key–#SQLNewBlogger

  1. Fernando Carvalho says:

    Hi Steve,

    this is a very simple example. Alright, we can alter a PK without recreating the entire table. But if someone reach a point that the PK is full, or it is a bad design of the table, or the table is very large (with hundreds of millions of records and some serious GB in storage).
    Performing alter table on this very large tables is an absolute nightmare. When we are talking about production DB and the alter table puts a lock on it for hours you have a very big problem in hands.
    Without talking about all the indexes that have to change because of the alter table on the PK. And foreign keys, and schema binding views….. 🙂
    In this scenarios it’s probably better to create a copy of the table (with the new type on the PK) and then move the data.
    I have implemented scenarios, where this approach saved more than 80% of the execution time.
    And gives you lots of other possibilities, like copying the data in batches so the log file doesn’t blow up the server, and the availability replicas can follow along…

Comments are closed.