• Luis Cazares - Tuesday, February 21, 2017 2:12 PM

    Please disregard Joe Celko's rant. His design, although theorically correct, miss the physical design considerations. Using a CHAR(10) would add 6 bytes of storage to each row on Personnel and every time a column needs to reference this table or even more depending on the indexes. IDENTITY is part of the RDBMS, it's available and should be used appropriately.
    The CHECK constraint is another subject that has been discussed with him and shown that it has many disadvantages.
    He loves to berate about not following best practices, but there's so many things that can be improved in the code that he posts that I won't even start with the corrections.

    Let me second what Luis said.  There is an IDENTITY and it makes an excellent clustering key.  It's what's known as an artificial key because it has nothing to do with the data.  The other, a natural key, identifies the row just by being what it is.  Granted, the natural key you pick may not be any good a few years down the road and it'll almost certainly add more than 4 or 8 bytes like Luis said.  When you consider that every row of every nonclustered index inherits the bytes of the clustering key, keeping the clustering keys small becomes important.  Remember, you have to read them and they have to be in memory to be able to use them.

    I speak practical a lot better than I speak theoretical.