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
, DocumentName NVARCHAR(200)
, DocumentDate DATETIME2
INSERT dbo.Document
       , DocumentName
       , DocumentDate
     (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')
SELECT top 10
  FROM dbo.Document AS d

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.


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.


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.

Posted in Blog | Tagged , , | Leave a comment

Capturing the Application Time

I’ve always thought that any times we recorded in a row were related to the actual data itself. Almost all of the time I’ve built software, we’ve assumed that the time a row is written or updated is the time we need to record as a timestamp. This works great for many applications and it certainly simplifies programming when the database can capture the time. System versioned tables are a great example of where we use time in the database as the time of record.
Over the years we’ve started to build more distributed applications that reach a widely dispersed audience. With the Internet today, it’s entirely possible that a row inserted by one user takes place in time zones far away from the next row inserted by a different user. What may be more confusing is that I may insert a row in GMT/UTC, but then update that row a day later in the Mountain Standard Time time zone. In fact, that’s something I’ve done.
The complexity of time in an application means that we can’t assume we know how best to capture or store time. While I think system versioned tables are a great addition to SQL Server, they are an incomplete one. We need the ability to use these tables with some sort of application time marker, not just the database server time.
This week I’m wondering how many of you might feel the same way. How many of you need to capture application time separately from the system time? Do you do that now, with some bespoke system? Perhaps you wish you could and this is a feature your system is missing.
There is a feedback request for Microsoft that asks if application time can be captured as a part of the system versioned timestamps. Regardless of whether or not you have the need, this is an important enhancement that would greatly improve the system versioned table feature. Cast a vote today, and let us know if you would use application time in addition to the time in your database server.
Steve Jones
Posted in Editorial | Tagged | Leave a comment

Building a Database Engine

I never wrote a database engine, unless you count writing code to read, write, and update flat files. I remember doing that early in my life with a friend as we tried to build our fantasy baseball game system. We needed a way to handle data, and use flat files, setting tokens to denote various “rows” and columns of data. I’m not sure that’s much of a database, but that’s the most I’ve done in building an engine.
Someone decided to tackle building a SQLite clone in C and wrote a series of articles on the process. They are an interesting read as the author works his way through adding new functionality for the engine. While I wouldn’t want to actually recreate a database engine that I needed, the exercise is interesting. My C knowledge is a little rusty, but I can follow along enough to appreciate the way that the application takes shape.
Writing software is often a challenge when we are creating a new system from scratch. It is often easier when we are trying to copy something that already exists, but there is still an effort to recreate all the functionality that already exists. However, it’s a good exercise and one that often helps software developers build stronger skills and practice their craft.
If you were going to practice writing some code, database or otherwise, what would you like to write? Would you attempt a database engine? Many of us know how quite a bit about how SQL Server works, but I don’t know if we’d actually want to recreate the code for some part of the system. When I learned how the memory-optimized tables were structured, I had fond memories of building similar linked lists in university, though at a far simpler (and less efficient) level.
I find myself tackling some problems to help others, or teach them a technique, but I haven’t had to build a full set of software in years. Maybe I’ll find a project at some point that I really want to tackle and actually build something larger. Finding the spare time to tackle a project is hard, but I’m ever hopeful that I’ll make the effort at some point.
Steve Jones
Listen to the podcast at Libsyn.
Posted in Editorial | Tagged | Leave a comment

Generating an SSH key on Windows

I needed an SSH key. First I needed to the WSL subsystem, and once I had that working, I could do the work I needed to do: get an SSH key.

There is a utility to help with this on Linux, called ssh-keygen. You run this at the command line and get two files output. These are the public and private keys. I knew how to do most of this, but what I wanted to do was get a custom filename. There are two ways to do this.

First, you can just enter ssh-keygen at the command line. The system will then prompt you for the filename, with id_rsa as the default.

2019-03-27 12_27_17-sjones@Plato_ _mnt_c_Users_Steve_.ssh

The other way is with the –f parameter. In this case, I use this because I want to specify this as an RSA key as well as set the bit size.

2019-03-27 12_26_16-sjones@Plato_ _mnt_c_Users_Steve_.ssh

In both cases, I now have new ssh keys I can use to connect to servers, once I add them to the server itself.

2019-03-27 12_28_19-.ssh

Posted in Blog | Tagged , | Leave a comment