re: Database Naming Conventions

Nathan Zaugg — Wed, 21 Jul 2010 22:47:38 GMT

Here is the 4-1-1 on why it's important to use identity columns over "natural" keys. Listed in order of importance.

* It is important for the health of a database to insert data into the clustered index in an ascending order. Background: There are two types of indexes called a Clustered Index and a Non-Clustered index. A clustered index is the physical ordering of the rows in the pages on the database. A non-clusterd index references the clustered index to make it's own lists of the order of the rows, but it just keeps a reference to the clustered index. You may only have one clustered index because you can only have one physical ordering of data. Okay, so now to the question "Why is it bad to insert rows out of order". The answer is that because the clustered index is physically ordered, if you have a row that goes in the middle of other rows, those other rows need to be shifted down. Actually it doesn't bother moving the data down, it performs something called a page tear. Because all rows are stored on 8K pages any rows behind the row that is inserted are simply copied to a new page. This leaves both pages half empty and means your database has to perform twice as many requests to the disk. This is a big deal! The performance will be acceptable in development (you may never notice) but in production it will cause nothing but problems.

* There really is no such thing as a natural key - there would be very few real-life examples of where the data is guaranteed to be unique (an imperative for being a primary key). It then takes very little time for an application to want to insert a duplicate record (violating the key) because the key was poorly selected. In fact, because of these problems, I very frequently see tables with almost ALL columns in the composite key because it takes nearly all columns to make the row unique. So what's the point of a key at all?

* Natural keys typically include more than a single value (see reason above). This makes it difficult to make relationships to other tables because each column that constitutes the key will have to be present on the child table to make that relationship.

* When you join two tables together, you typically join the tables on as key. For the parent table it will probably be a primary key and for the child table it will be a foreign key (non-clustered index). There are many ways the DBMS may choose to join the tables but typically it will be able to use the clustered index of the parent table and join the parent to the child. This will use a clustered-index scan which is the fastest. If both keys are non-clustered-indexes (because you are trying to avoid the page tearing on insert) the result is a hash-join or even a full table scan which are much slower! Also your join statements in SQL are going to get really long and ugly.

* If your primary key is missing or is an non-clustered index, a key is generated for you anyway. This is so it can distinguish between two different rows in the heap that have the same values and it really *needs* that row internally. For example, if you add a non-clustered index what would it point to? This also means that you will have two non-clustered indexes to deal with when joining tables together which hurts join performance (see point above)

* The surrogate key gains meaning in the application and is often used as the predicate in SQL queries.

* The table schema is more difficult to change. For example, you cannot move a field out of one table and into another when it is part of the natural key. The integrity of the entire table is put at risk.

Having said that, you can have a primary key that is a non-clustered index and you can opt for a different column to be your clustered index. This is a very a-typical configuration and can be helpful. Remember, clustered index seeks/scans are far faster than using a non-clustered index. If you constantly query on a field or set of fields that can be unique, you would see a big performance boost in those queries. Pretty much everything else you do with that table will suffer though and you still have the problem of getting it in there in order or keeping the database defragmented. Another potential gain is that the natural order of the rows will be the same as the clustered index. There are situations where you would want to take advantage of these side-effects but only an experienced DBA could make a good judgment call there.