Hands-On Busines Intelligence workshop with Brian Knight
Taught by the master, this seriously hands-on workshop will take you from start to finish with practical creation of a data warehouse solution. You’ll learn the real-world things you need to know, and you’ll be learning them from the master of BI, Brian Knight. The workshop will be held in the SSWUG.ORG offices, with team and individual tasks.
Due to the hands-on nature of this workshop,
SEATING IS EXTREMELY LIMITED.
Find out more here, and register ASAP to save your seat or seats for your entire team.
Primary Key vs. Alternate Keys
I recently had a reader ask the question, “what is the difference between a Primary key and an Alternate key?” Let’s take a look at the question.
First of all, let me say that this is an area where people have strong convictions and become very passionate.
In simple terms, a primary key is a constraint on a table uniquely identifying each row. No two rows may have the same key attribute. In some designs, more than one column determine the unique descriptor for a row. In the Microsoft world most of their tools drive you to having a unique sequential column in every table. Microsoft SQL Server has been tuned to perform efficiently by organizing data against a single incremental integer,.
So, if you are using Microsoft tools, why don’t you simply make the unique sequential column (IDENTITY) the primary key? This speaks to the points where people become passionate. A sequence is completely un-meaningful. Many tables refer to real world objects having real world identifiers. Some prefer using the real world identifiers as a primary key.
Just because a table has a primary key does not mean it cannot have an additional unique key identifying each row. These keys are known as Alternate Keys because they are unique and may function in a like manner as a primary key. Just as a Primary Key may be used for a foreign key relationship, an Unique Index may also be used as the source of a foreign key index.
Just a note, there is nothing keeping you from having multiple alternate keys on a table. However, most tables, if a reasonable amount of normalization has been done, do not contain many unique keys.
In short, a single table may have both a sequential column (for the Microsoft automated tools such as Entity Framework) as well as a natural key enforcing uniqueness in your data. For example, if you had a table containing products you sold, you could have a composite key consisting of the manufacturer, and the manufacturer part number. This combination may be unique across your entire inventory. If so, having a unique key based on this combination results in a more accurate set of data, and performance opportunities that you may not have using other techniques.
Including both the manufacturer/part number unique constraint, and a sequential column as a unique key allows you to implement both worlds.
Hopefully this has brought to your attention the need to really think about your keys in your database.
Send your responses to btaylor@sswug.org if you would like to share your Key knowledge.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
UNIX for DBAs (Part 3)
UNIX is one of the most popular operating system for running database management systems. Of course, Windows is popular, too, as are z/OS and even OS/400, not to mention Linux (but that is a variant of UNIX). At any rate, UNIX is ubiquitous in the realm of database processing and it is important that DBAs know the basics of UNIX in order to effectively manage the databases on that platform. This article offers up a short tutorial on the basic UNIX operating system commands and features that are most important for DBAs to understand.
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Written by Townsend Security
Simplify encryption and key management on … (read more)