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 […]

The default settings in SQL Server are usually quite performant. Just like many other data engines, there are things you can do to optimize the performance specific to your unique requirements. Often, the things you can do are based on physics. As the persistence engine matures, the number of things you can do is decreasing. Back to the comment about […]

SQL Server provides the User Defined Table Types as a method to create a pre-defined temp table. Additionally, because they are a defined object in a database, you can pass them around as parameters or variables from one query to another. They can even be read only input parameters to stored procedures. In sql you might do something like the […]

Why would you want to use windows service bus, hosted in Azure or on premise? The best answer is that it makes communication easy between your applications. How many times have you had to roll your own message queues in an SQL Table? How often do you configure replication between databases? Do you have multiple applications that need to be […]

SQL Server has supported the concept of Sparse Columns since 2008 R2. The purpose of the Sparse columns is to optimize storage and performance in those instances where you have a table containing a lot of null data. In my experience, this is often due to a schema that is not normalized. While normalization is generally the best policy, there […]

SQL Server supports creating extended properties for objects in your schema. Extended properties are the equivalent of a property bag, allowing you to create as many key/value pairs as you like on schema objects. Tables, columns, views, procedures are some of the SQL objects supporting extended properties. Tables are the most likely candidate, in my mind, for the use of […]

Today I want to consider an aspect of Microsoft SQL Server that I acknowledge I take for granted. When you consider the different applications that may be supported by the SQL Server engine(s), it is really quite amazing. You can use an embedded version, that may only run in memory. You can use a personal edition, freely distributable with your […]