Forwards Compatibility
In response to yesterday’s editorial I received a number of comments regarding backwards compatibility. I have been working with Microsoft SQL Server since version 4.21. Unlike many products I have used, SQL Server has been very generous in supporting deprecated features. Generally, the status of a feature is made known long before the product no longer supports it; I have seen features labeled as deprecated and not fully removed for two full releases.
The questions I received today wrap around two aspects of old and new features. Primarily this is due to the fact that we often have to support multiple instances concurrently. This support becomes more difficult when the same product is supported in multiple versions of SQL Server.
Yesterday I mentioned the new feature of Sequences being added to SQL Server 2012. This is a great feature when you have a normalized database using a type/sub-type schema. You may have a logical based type of Person, with many sub-type tables for Customer, Sales Person, Employee, Spouse, Dependent, Operator, etc. There are many kinds of people, and they all have similar characteristics such as names, phones, addresses, etc.
When you wish to have multiple tables for different kinds of people, but have a unique identifier across all instances in all tables, a sequence capability fills the bill quite handily. But wait, you still have customers out there using an older version of SQL Server. Manoj asks what we can do to support this kind of design in older versions of SQL Server?
In this case you would have to roll your own Sequence manager through tables, functions and/or stored procedures. My preference would be to create the environment inside SQL Server so that referential integrity and uniqueness could be confirmed readily.
If I had to support this personally, I would create a stored procedure and/or function wrapping around the technique I created for older versions of SQL Server, and around the sequences in the 2012 release. By creating this adapter procedure, I can write my code to call it instead, and all the magic for the different approaches is handled behind the scenes so my consumer code may be as simple as possible.
Tomorrow I’ll touch on the topic of Backwards Compatibility. As always, please send your comments to share with me and our readers to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
System Health Session Dashboard
I had written a series on how to create reports based on the system health session for SQL Server 2008 and above instances in the recent past. I got a lot of positive feedback regarding the reports which got me thinking on how to make this available using the new features of SQL Server 2008 R2 Reporting Services. Since the existing set of reports which I had published could be viewed from SQL Server Management Studio using the custom reports option, the reports had to conform to SQL Server 2005 Report schema. This in turn meant that I couldn’t make use of the new controls or cascading parameters features for these reports. This is the entire reason why I am starting a new series wherein you can use the solution mentioned to deploy to a SQL Server 2008 R2 Reporting Services instance and view the system health session reports for all the SQL Server 2008 and above instances in your environment.
Featured White Paper(s)
Demystify Tempdb Performance and Management
In this white paper, Robert L Davis demystifies the best practices that work some of the time, but not all of the time, when … (read more)
Featured Script
dba3_MS_ScriptsToTransferLoginsAndPasswords
The MS Scripts for the stored procedures to use to Transfer Logins and Passwords Between SQL Servers (Q246133)… (read more)