Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm a fan of surrogate keys. There is a risk my findings are confirmation biased.

Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys based on IDENTITY() values.

My background in computer systems tells me performing any comparative operation on an integer will be faster than comparing strings.

This comment made me question my beliefs, so I thought I would create a system to investigate my thesis that integers are faster than strings for use as keys in SQL Server.

Since there is likely to be very little discernible difference in small datasets, I immediately thought of a two table setup where the primary table has 1,000,000 rows and the secondary table has 10 rows for each row in the primary table for a total of 10,000,000 rows in the secondary table. The premise of my test is to create two sets of tables like this, one using natural keys and one using integer keys, and run timing tests on a simple query like:

SELECT *
FROM Table1
    INNER JOIN Table2 ON Table1.Key = Table2.Key;

The following is the code I created as a test bed:

USE Master;
IF (SELECT COUNT(database_id) FROM sys.databases d WHERE d.name = 'NaturalKeyTest') = 1
BEGIN
    ALTER DATABASE NaturalKeyTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE NaturalKeyTest;
END
GO
CREATE DATABASE NaturalKeyTest 
    ON (NAME = 'NaturalKeyTest', FILENAME = 
        'C:\SQLServer\Data\NaturalKeyTest.mdf', SIZE=8GB, FILEGROWTH=1GB) 
    LOG ON (NAME='NaturalKeyTestLog', FILENAME = 
        'C:\SQLServer\Logs\NaturalKeyTest.mdf', SIZE=256MB, FILEGROWTH=128MB);
GO
ALTER DATABASE NaturalKeyTest SET RECOVERY SIMPLE;
GO
USE NaturalKeyTest;
GO
CREATE VIEW GetRand
AS 
    SELECT RAND() AS RandomNumber;
GO
CREATE FUNCTION RandomString
(
    @StringLength INT
)
RETURNS NVARCHAR(max)
AS
BEGIN
    DECLARE @cnt INT = 0
    DECLARE @str NVARCHAR(MAX) = '';
    DECLARE @RandomNum FLOAT = 0;
    WHILE @cnt < @StringLength
    BEGIN
        SELECT @RandomNum = RandomNumber
        FROM GetRand;
        SET @str = @str + CAST(CHAR((@RandomNum * 64.) + 32) AS NVARCHAR(MAX)); 
        SET @cnt = @cnt + 1;
    END
    RETURN @str;
END;
GO
CREATE TABLE NaturalTable1
(
    NaturalTable1Key NVARCHAR(255) NOT NULL 
        CONSTRAINT PK_NaturalTable1 PRIMARY KEY CLUSTERED 
    , Table1TestData NVARCHAR(255) NOT NULL 
);
CREATE TABLE NaturalTable2
(
    NaturalTable2Key NVARCHAR(255) NOT NULL 
        CONSTRAINT PK_NaturalTable2 PRIMARY KEY CLUSTERED 
    , NaturalTable1Key NVARCHAR(255) NOT NULL 
        CONSTRAINT FK_NaturalTable2_NaturalTable1Key 
        FOREIGN KEY REFERENCES dbo.NaturalTable1 (NaturalTable1Key) 
        ON DELETE CASCADE ON UPDATE CASCADE
    , Table2TestData NVARCHAR(255) NOT NULL  
);
GO

/* insert 1,000,000 rows into NaturalTable1 */
INSERT INTO NaturalTable1 (NaturalTable1Key, Table1TestData) 
    VALUES (dbo.RandomString(25), dbo.RandomString(100));
GO 1000000 

/* insert 10,000,000 rows into NaturalTable2 */
INSERT INTO NaturalTable2 (NaturalTable2Key, NaturalTable1Key, Table2TestData)
SELECT dbo.RandomString(25), T1.NaturalTable1Key, dbo.RandomString(100)
FROM NaturalTable1 T1
GO 10 

CREATE TABLE IDTable1
(
    IDTable1Key INT NOT NULL CONSTRAINT PK_IDTable1 
    PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Table1TestData NVARCHAR(255) NOT NULL 
    CONSTRAINT DF_IDTable1_TestData DEFAULT dbo.RandomString(100)
);
CREATE TABLE IDTable2
(
    IDTable2Key INT NOT NULL CONSTRAINT PK_IDTable2 
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , IDTable1Key INT NOT NULL 
        CONSTRAINT FK_IDTable2_IDTable1Key FOREIGN KEY 
        REFERENCES dbo.IDTable1 (IDTable1Key) 
        ON DELETE CASCADE ON UPDATE CASCADE
    , Table2TestData NVARCHAR(255) NOT NULL 
        CONSTRAINT DF_IDTable2_TestData DEFAULT dbo.RandomString(100)
);
GO
INSERT INTO IDTable1 DEFAULT VALUES;
GO 1000000
INSERT INTO IDTable2 (IDTable1Key)
SELECT T1.IDTable1Key
FROM IDTable1 T1
GO 10

The code above creates a database and 4 tables, and fills the tables with data, ready to test. The test code I ran is:

USE NaturalKeyTest;
GO
DECLARE @loops INT = 0;
DECLARE @MaxLoops INT = 10;
DECLARE @Results TABLE (
    FinishedAt DATETIME DEFAULT (GETDATE())
    , KeyType NVARCHAR(255)
    , ElapsedTime FLOAT
);
WHILE @loops < @MaxLoops
BEGIN
    DBCC FREEPROCCACHE;
    DBCC FREESESSIONCACHE;
    DBCC FREESYSTEMCACHE ('ALL');
    DBCC DROPCLEANBUFFERS;
    WAITFOR DELAY '00:00:05';
    DECLARE @start DATETIME = GETDATE();
    DECLARE @end DATETIME;
    DECLARE @count INT;
    SELECT @count = COUNT(*) 
    FROM dbo.NaturalTable1 T1
        INNER JOIN dbo.NaturalTable2 T2 ON T1.NaturalTable1Key = T2.NaturalTable1Key;
    SET @end = GETDATE();
    INSERT INTO @Results (KeyType, ElapsedTime)
    SELECT 'Natural PK' AS KeyType, CAST((@end - @start) AS FLOAT) AS ElapsedTime;

    DBCC FREEPROCCACHE;
    DBCC FREESESSIONCACHE;
    DBCC FREESYSTEMCACHE ('ALL');
    DBCC DROPCLEANBUFFERS;
    WAITFOR DELAY '00:00:05';
    SET @start = GETDATE();
    SELECT @count = COUNT(*) 
    FROM dbo.IDTable1 T1
        INNER JOIN dbo.IDTable2 T2 ON T1.IDTable1Key = T2.IDTable1Key;
    SET @end = GETDATE();
    INSERT INTO @Results (KeyType, ElapsedTime)
    SELECT 'IDENTITY() PK' AS KeyType, CAST((@end - @start) AS FLOAT) AS ElapsedTime;

    SET @loops = @loops + 1;
END
SELECT KeyType, FORMAT(CAST(AVG(ElapsedTime) AS DATETIME), 'HH:mm:ss.fff') AS AvgTime 
FROM @Results
GROUP BY KeyType;

These are the results:

enter image description here

Am I doing something wrong here, or are INT keys 3 times faster than 25 character natural keys?

share|improve this question
2  
Sounds about right. You may want to go into more detail: estimate number of reads, CPU, and memory used. Besides performance, I would consider robustness of code as well: if your requirements change and your natural PK no longer works, you have to do expensive refactoring. –  A-K Sep 29 '13 at 2:19
1  
Well the INT is 4 bytes and the effective NVARCHAR(25) is about 14 times longer (including system data such as length), so in terms of index alone I believe that you would have a significantly wider and deeper PK index and therefore more I/O is needed which will impact processing time. Howevev a natural integer (perhaps even check digited) would be pretty much the same INT that we think of using for a surrogate Identity column. So, the "natural key" maybe an INT, BIGINT, CHAR, NVARCHAR and that all matters. –  RLF Sep 29 '13 at 2:25
4  
I think the performance gain @MikeSherrill'Catcall' was getting at is that you don't actually need the join against the "lookup" table when you use a natural key. Compare a query to get the lookup value with a join, with a query where the value is already stored in the main table. You might get a different "winner" depending on the natural key length and number of rows in the lookup table. –  Mikael Eriksson Sep 29 '13 at 5:43
2  
What @MikaelEriksson said plus the cases when you have a join between more than 2 tables (say 4) where with the surrogates you'll have to join tables A to D through B and C while with natural keys you could join A to D directly –  ypercube Sep 29 '13 at 7:15
1  
Collation does make a huge difference. A binary collation can be 20 times faster than a Unicode collation with complex linguistic rules. SQL collations are around 7 times faster, but slower than binary. In addition, SQL Server has a number of execution engine optimizations for single-column integer type keys, particularly if they are defined NOT NULL. –  Paul White Sep 30 '13 at 5:19

2 Answers 2

I believe, that the best lies in the middle.

Natural keys overview:

  1. They are make data model more obvious because they are came from object area, and not from somebody's head.
  2. Simple keys (one column, between CHAR(4) and CHAR(20)) are saving some extra bytes, but you need to watch for their consistency (ON UPDATE CASCADE becomes critical for those keys, that might be changed).
  3. A lot of cases, when natural keys are complex: consists of two or more columns. If such key might migrate to another entity as a foreing key, then it will add data overhead (indices and data columns might become large) and performance loose.
  4. If key is a large string, then it probably always will loose to an integer key, because simple search condition becames a byte array comparison in a database engine, which in most cases is slower, than integer comparison.
  5. If key is a multilanguage string then need to watch the collations also.

Benefits: 1 and 2.

Watchouts: 3, 4 and 5.


Artificial identity keys overview:

  1. You do not need to bother about their creation and handling (in most cases) as this feature handled by database engine. They are unique by default and doesn't take a lot of space. Custom operations like ON UPDATE CASCADE might be ommited, because key values not changing.

  2. They (often) are best candidates for migration as a foreign keys because:

    2.1. consist of one column;

    2.2. using a simple type which has a small weight and acts fast for comparison operations.

  3. For an association entities, which keys are not migrate anywhere, it might become a pure data overhead, as it usefulness is lost. Complex natural primary key (if there are no string columns there) will be more useful.

Benefits: 1 and 2.

Watchouts: 3.


CONCLUSION:

Arificial keys are more maintainable, reliable and fast because they have been designed for this features. But in some cases are not needed. For example, single CHAR(4) column candidate in most cases behaves like INT IDENTITY. So there is another question here also: maintainability + stability or obviousness?

Question "Should I inject an artificial key or not?" always depends on natural key structure:

  • If it contains a large string, then it is slower and will add data overhead if migrating as foreign to another entity.
  • If it consists of multiple columns, then it is slower and will add data overhead if migrating as foreign to another entity.
share|improve this answer
4  
"Custom operations like ON UPDATE CASCADE might be ommited, because key values not changing." The effect of surrogate keys is to make every foreign key reference the equivalent of "ON UPDATE CASCADE". The key doesn't change, but the value it represents does. –  Mike Sherrill 'Cat Recall' Sep 30 '13 at 12:04
    
@MikeSherrill'Catcall' Yes, of course. However, ON UPDATE CASCADE not used, while keys never been updated. But, if they are, then it could be a problem if ON UPDATE NO ACTION is configured. I mean, that DBMS never using it, while key column values not changed. –  HAL9000 Sep 30 '13 at 12:43

In general, SQL Server uses B+Trees for indexes. The expense of an index seek is directly related to the length of the key in this storage format. Hence, a surrogate key usually outperforms a natural key on index seeks.

SQL Server clusters a table on the primary key by default. The clustered index key is used to identify rows, so it gets added as included column to every other index. The wider that key, the larger every secondary index. Even worse, if the secondary indexes are not explicitly defined as UNIQUE the clustered index key automatically becomes part of the key of each of those. That usually are most secondary indexes as usually only indexes created to enforce uniqueness are declared as unique.

So if the question is, natural versus surrogate clustered index, the surrogate will almost always win.

On the other hand, you are adding that surrogate column to the table making the table in itself bigger. That will cause clustered index scans to get more expensive. So, if you have only very few secondary indexes and your workload requires to look at all (or most of the) rows often, you actually might be better of with a natural key saving those few extra bytes.

Finally, natural keys often make it easier to understand the data model.

So, as so often in the database world, the real answer is "it depends". And - always test in your own environment with realistic data.

share|improve this answer
    
"it depends" - AMEN! –  Max Vernon Sep 29 '13 at 3:26
1  
Can you explain how a natural PK is easier to understand than a UNIQUE constraint on the same column(s)? –  A-K Sep 29 '13 at 3:29
2  
@AlexKuznetsov, while using more storage space, natural primary keys lead to natural foreign keys which in turn increase local information density. –  Sebastian Meine Sep 29 '13 at 16:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.