StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In all but the simplest internal situations, I recommend always going for the surrogate key. It gives you options in the future, and protects you from unknowns.</p> <p>There's no reason why additional keys, like an SKU, couldn't be made non-null to enforce them, but at least by removing your reliance on third-parties you're giving yourself the option to choose, rather than having it taken from you and enduring a painful rewrite at a later stage.</p> <p>Whether you go for the auto-incremented integer or determine the next primary key yourself, there will be complications. With the auto-incremented method, you can insert the record easily and let it assign its own key, but you may have trouble identifying exactly what key your record was given (and getting the max key isn't guaranteed to return yours).</p> <p>I tend to go for the self-assigned key because you have more control and, in sql server, you can retrieve your key from a central keys table and ensure nobody else gets the same key, all in one statement:</p> <pre><code>DECLARE @Key INT UPDATE KeyTable WITH (rowlock) SET @Key = LastKey = LastKey + 1 WHERE KeyType = 'Product' </code></pre> <p>The table records the last key used. The sql above increments that key directly in the table and returns the new key, ensuring its uniqueness.</p> <p><strong>Why you should avoid alphanumeric primary keys:</strong></p> <p>Three main problems: performance, collation and space.</p> <p>Performance - there is a performance cost though, like Razzie below, I can't quote any numbers, but it is less efficient to index alphanumerics than numbers.</p> <p>Collation - your developers may create the same key with different collations in different tables (it happens) which leads to constantly using the 'collate' commands when joining these tables in queries and that gets old really quickly.</p> <p>Space - a nine-character SKU like David's takes nine bytes, but an integer takes only four (2 for smallint, 1 for tinyint). Even a bigint takes only 8 bytes.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload