Featured White Paper(s)
VIDEO WHITEPAPER – How to Avoid Downtime – VIDEO WHITEPAPER
In this Experts & Insights video, we take a close-up look at the data protection challenges and solutions surrounding downtim… (read more)
Featured Script
admin script – change the SID of a login without loss of security settings
To avoid problems with orphaned users when you need to move a database between servers frequently, it is useful to have the l… (read more)
Denali Includes Sequences
Before you say this is no big deal, let me demonstrate how sequences can be of benefit.
I wrote an article earlier in the year called Emulate Inheritance In Your Relational Database demonstrating the need to share a sequence value across multiple tables. In short, there are tables that contain data that is common to many different kinds of entities in your database.
For example, a business and a person have different attributes in their base entity (Business, Person). However, they both share common attributes such as an address, telephone, email or URL, Notes, external Identifiers that may be encrypted such as Federal Identifiers, Credit Card Numbers, Health Care Identifiers, etc.
In order to share these attribute tables with both Business and Person tables, you must be sure that no primary key for a Business may be contained in a Person table. I generally do this using an Entity table that maintains an Identity column. Each Business record, or Person record must first have a record created in the Entity table, and then have a foreign key to the Entity table. Each entity record must point to only a Business or Person, but never both. You must enforce this uniqueness in SQL Server yourself.
Denali now supports the Sequence object. The sequence object is something Oracle has had for years. What this allows you to do is to create a sequence that works much the same as an Identity column. This difference is that you apply it to your table not with an IDENTITY definition, but with a default constraint on the column you wish to use for your sequence.
This was not possible with User Defined Functions in SQL 2008 because a UDF is not allowed to make modifications to data stored in your tables. There are other techniques that may work, but require operations external to SQL Server to full a UDF into allowing incrementing. You could create your own CLR Function storing data external to SQL Server, and thus enforcing its own locks, or use an External Stored Procedure called by a UDF. Either of these methods require excellent coding skills for parallel execution, locking, etc. They may also be a cause for Memory Leaks when used in SQL Server.
In essence, you now have a handy way you can share a sequencer with an unlimited number of tables and be assured that no record in any table has a duplicate value. I am guessing that you will not be able to do is use @@IDENTITY or SCOPE_IDENTITY() to determine the value that was assigned since you are simply using a default constraint to assign the value in your table, just like GETDATE(), or other functions. For this problem you will have to query back the row you added, or use the OUTPUT clause introduced in SQL Server 2005. If using the OUTPUT clause, you will most likely want to use Stored Procedures.
If you’re interested in seeing more about how the sequence is created and utilized, here is a good blog by SQLDenis with how to use them.
For those of you who are already using Type/Subtype database designs, how about sharing your tips for how to introduce a global sequence value. Send your comments to btaylor@sswug.org.
Cheers,
Ben