After seeing another SQL Server database this week without what I consider to be ‘the basics’, it got me to thinking what the basics are. In other words, what are the core fundamental practices we should follow every time we create a new database. The problem database that I saw this week had several tables without clustered indexes. In my world that wouldn’t happen. Every database table I create, even in a small, simple, or temporary database, always gets a clustered index right off the bat.
Here are the things I always do when creating a database or adding tables:
1. Every table has a Clustered Index.
2. I always assign a Primary Key.
3. I always create a Surrogate Key.
4. I always assign an appropriate data type to each column, so if a column is a number I always make its data type numeric, if its a date I make it datetime. I don’t get lazy and make these strings.
5. I almost always put an identity column on the table that auto increments. I can’t say I do this every time, but I don’t do it only when I have a good reason.
What kinds of things do you always do?
I find when you don’t do these things at the start, they can be forgotten later. I suspect that’s what happened with the database I reviewed this week. It’s been changed and added to so much over the years that I believe that some tables that created ‘temporarily’ in development ultimately became the real thing, and nobody went back and updated them.
It might be drudgery, but it saves a lot of pain and effort later when things are created right from the start.