I recently (yesterday, in fact), got to see some amazing recommendations regarding the use of data types. Now I have the chance to provide my personal opinions (guidelines):
- primary keys: monotonous, ever increasing values – personal choice, but it can introduce unnecessary complexity. So, for the case of small tables, use whatever fits you:
as an example, I have a table with the primary key set to a char(1), which holds 4-5 unique values (something like I, M, C, S). It’s a “large” table, something in the line of 2-3 KB, so I don’t bother changing it just for the sake of introducing unnecessary joins.
- if you search for a particular column, index it – actually, this is the very first time I heard this:-). I will blog about indexes a lot, later on, but suffice to say that nope, don’t you ever to that.
It is worth mentioning that Kimberly Tripp (let’s just say that she’s the guru when it comes to indexes) recommends indexing the foreign keys (non-clustered indexes), but this scenario is applicable in few situations. On a high-load production OLTP it might not be such a wise choice, as I have discovered myself a while ago. Yes, she also mentions the fact that there are scenarios in which it is not applicable.
- the thing for which wars have started: how “large” is a large table – I’m playing with virtual machines (as an MCT) and I query tables that have 10 million rows without a fuss, and all this on a laptop. Saying that one should split tables because they “seem large”… hm… let’s digress a bit.
I also got to see a database broken down into smaller databases, based on a particular criteria, let’s say “month”. Now the nightmare begins: query 10-12 databases instead of one. Not to mention the monotonous, ever increasing (sorry, I couldn’t help it) transaction logs for the plethora of databases that will be on the server after say, two years. Now, going back to our discussion…
breaking up databases like that is because they “seem large”. A simple table partitioning would have solved the problem of large tables (subject of another post).
So, my point is: bad advice comes through the same channels as good advice does. I feel like being a good guy today, so I’m giving you a link that might lighten up the choice: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/bad-habits-to-kick-using-the-wrong-data-type.aspx.
Have a good day ahead