I am finally back in the writing saddle again with a spree of tidbits for the month of April 2015. These articles will focus on learning a simple TSQL command or technique.
Today, I want to talk about how synonyms can be used to preserve backward compatibility objects while allowing for schema redesign in existing databases.
The CREATE SYNONYM and DROP SYNONYM key words were added to the TSQL language in the 2005 version. The main purpose of the construct is to provide a simple name to reference a database object that is addressed by a two, three or four part notation.
I think this construct has been over looked by developers who’s main job is to maintain older systems.
For instance, lets make believe we have a banking system that has three tables [CUSTOMERS], [ACCOUNTS] and [TRANSACTIONS] that was created before SQL Server 2005. All the tables reside in the [dbo] schema. However, managing objects at a schema level simplifies security. We grant rights to a group of tables, not to a single table.
Now, during a software maintenance project we have the opportunity to move all three tables to a [DAILY] schema. The most important goal of the project is to maintain compatibility with existing code.
How do we accomplish this feat?
Do not fret, the we can use synonyms to main backward compatibility.
The first step is to create a very simple database. Assuming that you have a MSSQL directory on the C: drive with two sub-directories named DATA and LOG, the code below will create the sample database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
/* 1 - Sample database */ -- Which database to use. USE [master] GO -- Delete existing database IF EXISTS (SELECT name FROM sys.databases WHERE name = N'BANK') DROP DATABASE [BANK] GO -- Define the db with default groups - on premise CREATE DATABASE [BANK] ON PRIMARY ( NAME = 'BankData', FILENAME = 'C:\MSSQL\DATA\Bank_Data.mdf', SIZE = 64MB, MAXSIZE = 128MB, FILEGROWTH = 32MB) LOG ON ( NAME = 'BankLog', FILENAME = 'C:\MSSQL\LOG\Bank_Log.ldf', SIZE = 32MB, MAXSIZE = 64MB, FILEGROWTH = 32MB) GO |
The second step is to create the customer table. The snippet below has a typical insert statement that might be coming from our legacy application.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
/* 2 - Sample table */ -- Which database to use. USE [BANK] GO -- Create new table CREATE TABLE [DBO].[CUSTOMERS] ( [cus_id] [int] NOT NULL, [cus_lname] [varchar](40) NULL, [cus_fname] [varchar](40) NULL, [cus_phone] [char](12) NULL, [cus_address] [varchar](40) NULL, [cus_city] [varchar](20) NULL, [cus_state] [char](2) NULL, [cus_zip] [char](5) NOT NULL, [cus_date_str] [varchar](10) NOT NULL ) GO -- Primary key constraint ALTER TABLE [DBO].[CUSTOMERS] ADD CONSTRAINT PK_CUS_ID PRIMARY KEY CLUSTERED (cus_id); GO -- Sample transaction INSERT INTO [DBO].[CUSTOMERS] VALUES (1, 'Miles','Alyssa', '828-714-5985','Deptford High Street', 'Lake Junaluska', 'NC', '28745', '2011-07-29'); GO -- Show the data SELECT * FROM [DBO].[CUSTOMERS]; GO |
The third step is to create the new schema to hold tables that have common business functionality. This type of grouping allows security to be given out at the schema level instead of the table level.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/* 3 - Create new schema */ -- Which database to use. USE [BANK] GO -- Delete existing schema. IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'DAILY') DROP SCHEMA [DAILY] GO -- Add new schema CREATE SCHEMA [DAILY] AUTHORIZATION [dbo]; GO |
The fourth step is to move the table to the new schema. A synonym is created to maintain backward compatibility.
Please note, that some TSQL statements are smart enough to not be fooled by synonyms. For example, the TRUNCATE TABLE statement knows that a table name is not being supplied but DELETE statement does not care.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
/* 4 - Move table 2 schema and create synonym */ -- Move table to schema ALTER SCHEMA [DAILY] TRANSFER [DBO].[CUSTOMERS]; GO -- Maintain compatibility CREATE SYNONYM [DBO].[CUSTOMERS] FOR [DAILY].[CUSTOMERS]; GO -- Some commands work DELETE FROM [DBO].[CUSTOMERS]; GO -- Re-try sample transaction INSERT INTO [DBO].[CUSTOMERS] VALUES (1, 'Miles', 'Alyssa', '828-714-5985', 'Deptford High Street', 'Lake Junaluska', 'NC', '28745', '2011-07-29'); -- Some commands do not work TRUNCATE TABLE [DBO].[CUSTOMERS]; GO |
Last but not least, a TSQL developer should test the existence of an object before creating a new one. If the object exists, one solution is to drop the object before creation. Please note that ALTER SYNONYM syntax does not exist!
1 2 3 4 5 6 7 8 |
/* 5 - Remove the synonym */ -- Delete existing synonym IF EXISTS (SELECT name FROM sys.synonyms WHERE name = N'CUSTOMERS') DROP SYNONYM [CUSTOMERS] GO |
To recap, I think that the SYNONYMS are a under used feature in the TSQL language.
Another good use of them is to point to reference tables in a [COMMON] database from multiple [BANKING] database shards. This assumes all databases are accessible to each other. Thus, we do not have to duplicate the [ZIPCODE] table to have it accessible to all databases.
While we have only talked about referencing tables in this simple example, SYNONYMS can reference functions, stored procedures and views. Please see the MSDN page for more details.
In the future, I hope you do not pass over the use of SYNONYMS when re-organizing your database schemas.