Can you make it work? Those are words I love to hear, or make me cringe depending on the circumstances. What makes the difference? The point in the lifecycle of the product that doesn’t work. When a project is early in the coding or implementation, change is easier to accommodate. When you are in production, change is much more difficult […]

Indexes are very important for SQL Server today. As your database grows, the more value indexes play in the performance of your SQL Server. However, this may not be the case as we move into the future. A good example of this prediction is the performance of systems like Big Table, the engine behind your Google searches. Instead of growing […]

Keeping your indexes tuned up is essential for every database, without exception. This is a dogmatic statement that is easy to defend. You say your database is small, so index tuning has little value. My response would be that because your database is small, the indexes remain adequately tuned by default. You say your database has very little change and […]

We’ve been talking about keys and indexes for the last few days. The keys you choose, and the indexes you create implement the basic business rules of your database when it comes to relationships between tables, and enforcing uniqueness of data. These rules may be defined as table constraints, but the implementation used by the SQL Engine is always based […]

We have touched on Fill Factor a couple of times recently. This is a concept I thought would prove useful to dig into a little deeper for some of our readers who are not as familiar with how data is stored in SQL Server. As I stated earlier, data is stored physically inside a table that is designed internally as […]

When talking about using natural keys the last couple of days, the most frequent comment against this technique is the issue of performance. I have databases where using natural keys outperforms using sequential, system assigned keys. Today I am going to posit a theory as to why that is so (other than I am just fooling myself, or my measurements […]

I had forgotten how much polarization there is regarding the use of System Assigned or Natural Keys. It seems to be an issue were we tend to have VERY strong opinions, mostly based on bad experiences we don’t want to re-visit. If we had a bad experience using Natural keys we lean toward system assigned keys. Those with issues caused […]

Today we are going to return to the topic of a natural key. There are two kinds of keys which have become popular as the unique identifier in relational database tables. The first is a system assigned unique key. This is most often a sequential integer value. It may be a GUID or other randomly assigned value. The second kind […]

In a comment from yesterday’s editorial, Kenneth Ambrose writes, “it’s quite a sad commentary on our industry that 90% of the people defining and implementing tables do not understand what a natural key is.” I have no way to validate the 90% claim, but, I have found through experience that the use of only system assigned keys is used in […]

One common query is to find and remove duplicate records from a set of data. Duplicate data often occurs in databases containing a primary key based on a sequential ID, as is common in many databases. While having a sequential ID is not wrong, if there is not alternate key, or unique index based on the natural key values of […]