Have you found an opportunity to use Sequences in SQL Server Yet? Sequences can be very powerful when implementing a type/subtype database pattern. Using a type/Subtype pattern you may have a number of tables that have similar characteristics. For example, you could have a number of different tables representing a person. You could have people tables like Employee, Sales Representative, Customer, Dependent, Packager, etc.
Since all of these tables have a common concept they could easily share a set of tables such as address, phone, etc. One of the easiest ways for these tables to share child tables is to assure that they do not have the same primary key.
Many times we use identity columns to generate a unique identifier for a record. This won’t work in subtype tables because an identity column is unique to a single table. Now you can take advantage of the sequence capability built into SQL Server.
You can create a person sequence. Then each time you create a new record in one of the subtype tables you get the next value from the person sequence. In this manner, all the subtype tables share the same sequence, and do not have overlapping primary keys. Since the keys are unique for each record across all of the sub-type tables you can create records in child tables knowing it will not overlap.
Are you using Sequences? Why not leave a comment here to share ways they have been effective in your experience? Or, drop me an Email at btaylor@sswug.org.
Cheers,
Ben