2

A while back, I asked if surrogate keys provide better performance than natural keys in SQL Server. @sqlvogel provided an answer to that question yesterday that caused me to revisit it.

This question is an attempt to "upgrade" the prior question, and hopefully provide the opportunity for thoughtful answers that help the community.

Consider a system for storing details about computers. Each computer has an architecture, and an Operating System. In SQL Server, we could create these tables using natural keys like this:

CREATE TABLE dbo.Architecture
(
    ArchitectureName varchar(10) NOT NULL
    , ArchitectureVersion decimal(5,2) NOT NULL
    , ReleaseDate date NOT NULL
    , CONSTRAINT PK_Architecture
        PRIMARY KEY CLUSTERED
        (ArchitectureName, ArchitectureVersion)
);

CREATE TABLE dbo.Manufacturer
(
    ManufacturerName varchar(10) NOT NULL
        CONSTRAINT PK_Manufacturer
        PRIMARY KEY CLUSTERED
);

CREATE TABLE dbo.OS
(
    OSName varchar(30) NOT NULL
    , ManufacturerName varchar(10) NOT NULL
        CONSTRAINT FK_OS_Manufacturer
        FOREIGN KEY
        (ManufacturerName)
        REFERENCES dbo.Manufacturer(ManufacturerName)
    , ArchitectureName varchar(10) NOT NULL
    , ArchitectureVersion decimal(5,2) NOT NULL
    , CONSTRAINT FK_OS_Architecture
        FOREIGN KEY 
        (ArchitectureName, ArchitectureVersion)
        REFERENCES dbo.Architecture(ArchitectureName, ArchitectureVersion)
    , CONSTRAINT PK_OS
        PRIMARY KEY CLUSTERED
        (OSName)
);

CREATE TABLE dbo.Computers
(
    ComputerID varchar(10) NOT NULL
        CONSTRAINT PK_Computers
        PRIMARY KEY CLUSTERED
    , OSName varchar(30) NOT NULL
        CONSTRAINT FK_Computers_OSName
        FOREIGN KEY 
        REFERENCES dbo.OS(OSName)
    , ComputerManufacturerName varchar(10) NOT NULL
        CONSTRAINT FK_Computers_Manufacturer
        FOREIGN KEY 
        REFERENCES dbo.Manufacturer(ManufacturerName)
    , EffectiveDate datetime NOT NULL
        CONSTRAINT DF_Computers_EffectiveDate
        DEFAULT (GETDATE())
    , ExpiryDate datetime NULL
);

To query the dbo.Computers table, with 2 rows in dbo.Computers, showing various details, we could do this:

SELECT Computers.ComputerID
    , Computers.ComputerManufacturerName
    , OSManufacturer = OS.ManufacturerName
    , Computers.OSName
    , OS.ArchitectureName
    , OS.ArchitectureVersion
FROM dbo.Computers
    INNER JOIN dbo.OS ON Computers.OSName = OS.OSName
WHERE Computers.EffectiveDate <= GETDATE()
    AND (Computers.ExpiryDate >= GETDATE() OR Computers.ExpiryDate IS NULL)
ORDER BY Computers.ComputerID;

The query output is:

╔════════════╦══════════════════════════╦════════════════╦════════════╦══════════════════╦═════════════════════╗
║ ComputerID ║ ComputerManufacturerName ║ OSManufacturer ║ OSName     ║ ArchitectureName ║ ArchitectureVersion ║
╠════════════╬══════════════════════════╬════════════════╬════════════╬══════════════════╬═════════════════════╣
║ CM700-01   ║ HP                       ║ Microsoft      ║ Windows 10 ║ x64              ║ 1.00                ║
║ CM700-02   ║ HP                       ║ Microsoft      ║ Windows 10 ║ x64              ║ 1.00                ║
╚════════════╩══════════════════════════╩════════════════╩════════════╩══════════════════╩═════════════════════╝

The query plan for this is quite simple:

enter image description here

Or, if we choose to use surrogate keys, like this:

CREATE TABLE dbo.Architecture
(
    ArchitectureID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Architecture
        PRIMARY KEY CLUSTERED
    , ArchitectureName varchar(10) NOT NULL
    , ArchitectureVersion decimal(5,2) NOT NULL
    , ReleaseDate date NOT NULL
    , CONSTRAINT UQ_Architecture_Name
        UNIQUE
        (ArchitectureName, ArchitectureVersion)
);

CREATE TABLE dbo.Manufacturer
(
    ManufacturerID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Manufacturer
        PRIMARY KEY CLUSTERED
    , ManufacturerName varchar(10) NOT NULL
);

CREATE TABLE dbo.OS
(
    OS_ID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_OS
        PRIMARY KEY CLUSTERED
    , OSName varchar(30) NOT NULL
        CONSTRAINT UQ_OS_Name
        UNIQUE
    , ManufacturerID int NOT NULL
        CONSTRAINT FK_OS_Manufacturer
        FOREIGN KEY
        REFERENCES dbo.Manufacturer(ManufacturerID)
    , ArchitectureID int NOT NULL
        CONSTRAINT FK_OS_Architecture
        FOREIGN KEY 
        REFERENCES dbo.Architecture(ArchitectureID)
);

CREATE TABLE dbo.Computers
(
    ComputerID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Computers
        PRIMARY KEY CLUSTERED
    , ComputerName varchar(10) NOT NULL
        CONSTRAINT UQ_Computers_Name
        UNIQUE
    , OS_ID int NOT NULL
        CONSTRAINT FK_Computers_OS
        FOREIGN KEY 
        REFERENCES dbo.OS(OS_ID)
    , ComputerManufacturerID int NOT NULL
        CONSTRAINT FK_Computers_Manufacturer
        FOREIGN KEY 
        REFERENCES dbo.Manufacturer(ManufacturerID)
    , EffectiveDate datetime NOT NULL
        CONSTRAINT DF_Computers_EffectiveDate
        DEFAULT (GETDATE())
    , ExpiryDate datetime NULL
);

In the design above, you may notice we have to include several new unique constraints to ensure our data model is consistent across both approaches.

Querying this surrogate-key approach with 2 rows in dbo.Computers looks like:

SELECT Computers.ComputerName
    , ComputerManufacturerName = cm.ManufacturerName
    , OSManufacturer = om.ManufacturerName
    , OS.OSName
    , Architecture.ArchitectureName
    , Architecture.ArchitectureVersion
FROM dbo.Computers
    INNER JOIN dbo.OS ON Computers.OS_ID = OS.OS_ID
    INNER JOIN dbo.Manufacturer cm ON Computers.ComputerManufacturerID = cm.ManufacturerID
    INNER JOIN dbo.Architecture ON OS.ArchitectureID = Architecture.ArchitectureID
    INNER JOIN dbo.Manufacturer om ON OS.ManufacturerID = om.ManufacturerID
WHERE Computers.EffectiveDate <= GETDATE()
    AND (Computers.ExpiryDate >= GETDATE() OR Computers.ExpiryDate IS NULL)
ORDER BY Computers.ComputerID;

The results:

╔══════════════╦══════════════════════════╦════════════════╦════════════╦══════════════════╦═════════════════════╗
║ ComputerName ║ ComputerManufacturerName ║ OSManufacturer ║ OSName     ║ ArchitectureName ║ ArchitectureVersion ║
╠══════════════╬══════════════════════════╬════════════════╬════════════╬══════════════════╬═════════════════════╣
║ CM700-01     ║ HP                       ║ Microsoft      ║ Windows 10 ║ x64              ║ 1.00                ║
║ CM700-02     ║ HP                       ║ Microsoft      ║ Windows 10 ║ x64              ║ 1.00                ║
╚══════════════╩══════════════════════════╩════════════════╩════════════╩══════════════════╩═════════════════════╝

enter image description here

The I/O statistics are even more telling. For the natural keys, we have:

Table 'OS'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  
Table 'Computers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  

For the surrogate key setup, we get:

Table 'Manufacturer'. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Architecture'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OS'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Computers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Quite clearly, in the above admittedly very simple setup, the surrogate key is lagging in both ease-of-use, and performance.

Having said that, what happens if we need to change the name of one of the manufacturers? Here's the T-SQL for the natural key version:

UPDATE dbo.Manufacturer 
SET ManufacturerName = 'Microsoft­™' 
WHERE ManufacturerName = 'Microsoft';

And the plan:

enter image description here

The T-SQL for the surrogate key version:

UPDATE dbo.Manufacturer 
SET ManufacturerName = 'Microsoft­™' 
WHERE ManufacturerID = 1;

And that plan:

enter image description here

The natural key version has an estimated subtree cost that is nearly three times greater than the surrogate key version.

Am I correct in saying that both natural keys and surrogate keys offer benefits; deciding which methodology to use should be carefully considered?

Are there common situations where the comparisons I made above don't work? What other considerations should be made when choosing natural or surrogate keys?

  • You might consider applying this on a larger scale before jumping to a conclusion about surrogate or natural keys. Also the definition of a natural key is imperative. For example, textbook definition is a value that contains real world values that can be used to identify A row. In other words, it may be considered unsafe at best and often is really a composite key. – clifton_h Aug 30 '17 at 23:04
  • 2
    @clifton - i purposely did not insert a large amount of data based on not looking at performance from a real throughput scenario, but instead looking at plan shape and I/O estimates. I realize also that this sample only reads a single page for each table involved. Tomorrow i will add a ton of data to each scenario and report those findings. I also intend on adding sample insert statements to enable easier repros. – Max Vernon Aug 30 '17 at 23:22
  • Maybe another way to say it is what is the purpose of those keys. If the aim is performance upon reads? Then there must be a valid reason for this and likely a surrogate key (perhaps even a durable key) is called for. If there is a concern about obsfucating non-database let's, natural keys are never ok. If there is a need to flatten the tables for ease of reference (like in a report) natural may be appropriate. My point is there must be a business reason that the design is considering. Anything else is meaningless to the real-world. – clifton_h Aug 30 '17 at 23:26
0

I think your question title, apart from the fact that it is not a question, is a bit misleading. You're not measuring performance of natural/surrogate keys, unlike the question you're referencing. Instead you're measuring performance of queries against two databases with notably different models, caused by the choice of their respective primary keys. What is the purpose of the Manufacturer entity in your model? I understand that it's a contrived example, but that is exactly its weakness as an example.

Clearly a query that needs 3 joins instead of 1 will require more I/O. Same (or worse) is with updating a parent key value.

Your first example essentially shows the effect of "denormalization" on query performance, which is trivial. Sure, it's not denormalization strictly speaking, but it effectively amounts to merging contents of what normally would be a lookup table (Manufacturer) into the table referencing it, avoiding a join to look up the manufacturer name. Ditto Architecture.

The second example affirms the opinion that primary keys should never be updated. Try instead

UPDATE dbo.Manufacturer 
SET ManufacturerID = 1 
WHERE ManufacturerID = 1;

and you'll probably get the same outcome as you do with the natural key.

Finally, to answer your question as stated: yes, so what?

  • 5
    That's one advantage of using natural keys (as FKs in tables that reference them), that joins can often be eliminated. But is irrelevant to (de)normalization. – ypercubeᵀᴹ Aug 30 '17 at 21:09
  • You should state that his example does not provide enough depth to validate anything beyond is simplistic two queries. The rest of your argument is not helpful, as you again should be answering how his test did not account enough for the fixed variables necessary to proof his theory. – clifton_h Aug 30 '17 at 22:57
  • 1
    There can be a manufacturer for OSes as well as Computers. I've included that the same way in both scenarios. I may be misunderstanding, but I dont see how that is a weakness. – Max Vernon Aug 31 '17 at 1:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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