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

Optimizing your data warehouse load processes can be a difficult problem to crack. The more there is normalization in your warehouse, such as using a snowflake schema, the harder it can be to import large volumes of data. Yet, that is the main purpose of a data warehouse; to store and mine large volumes of data. The reason it becomes […]