No Clustered Index

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: