• When I saw the subject of today's featured article in the daily newsletter, I was enthusiastic. But after reading the article, I am quite disappointed.

    I was enthusiastic because there are many misconceptions and bad practices around surrogate keys, and I hoped this article would help eradicate them. But instead, the article itself falls victim to one of the most common misconceptions.

    A surrogate key should never replace the "business" key. It should be provided as an additional key, side by side with the original one. If ProductNumber identifies a product in the first table (without the surrogate key), it is also a candidate key in the second table (with surrogate). To prevent duplicate data entry, it should be declared as such, using a UNIQUE constraint. The table design screenprints posted don't carry this information, but the posted details from the execution plan of the UPDATE statement clearly show that such a constraint is not in place - otherwise, the execution plan would have been much more efficient.

    This renders all the comparisons in the article completely useless. A properly implemented surrogate key always goes hand in hand with a UNIQUE constraint on the "business" key, so such an implementation should be the basis of all comparisons.

    (And that being said, I also question the usefullness of a comparison that is based on one single query and one single update. Plus, the use of the estimated plan instead of the actual execution stats also make the exercise less meaningful, as the estimated plan can sometimes be completely off).

    But I'll remain positive, and hope that the next part(s) of this article will be better!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/