When a SQL Server synonym is created with T-SQL the base object's existence is checked only at run time. SQL Server Management Studio (SSMS) has some built-in checks that are performed during synonym creation whereas T-SQL scripts can be written several different ways and do not have any checks by default. What is the best way to create synonyms and why?
Synonyms became available in SQL Server since version 2005. One of the great uses of synonyms is described in this tip.
Synonyms similar to other SQL Server database objects could be created using the SSMS GUI or using T-SQL scripts. In this tip we will show the difference between the two methods and provide you a template for synonym creation.
Create a synonym with SSMS
To create a synonym with SSMS, under the database right click the Synonyms container and click "New Synonym...":
- Type a Synonym name
- Select "Database name" or keep default/current database if the base object is in the same database where synonym is created
- Select "Schema"
- Select "Object type" from the drop-down list
- Select "Object name" from the drop-down list (it will be populated after the Object Type is selected):
Note: the "Object name" drop-down list will be populated with object names only after the schema and object type are selected.
SELECT name, base_object_name FROM sys.synonyms GO
When you use the selection options in SSMS for the database name, schema and object name you don't have to worry about the object's existence. But you can overwrite the database, schema and object name and type them in manually. In this case you run the risk of mistyping or entering a non-existent object. SSMS will create the synonym just fine, but it will reference a non-existing object.
Create a synonym with T-SQL
When a synonym is created using T-SQL for the base object residing in the same database it can be created with a two-part name instead of a three-part name:
CREATE SYNONYM [dbo].[TempSyn_2] FOR [dbo].[v3] GO
In theory, neither the database name or schema name is required when T-SQL is used for the synonym creation. If the database name is not specified then the name of the current database is used. If the schema name is not specified then the default schema of the current user is used.
CREATE SYNONYM [dbo].[TempSyn_3] FOR [_Demo]..[Table_1] GO CREATE SYNONYM [dbo].[TempSyn_4] FOR [Table_1] GO
Here is what we have as a result of querying sys.synonyms view:
SELECT name, base_object_name FROM sys.synonyms GO
SSMS on the other hand will raise an error if you try to create a synonym without a schema name:
or without a database name:
SQL Server Synonym Best Practices
To make the base object name in the sys.synonyms view consistent you should always use three-part names when you create synonyms with T-SQL. Here is what this data looks like if we query sys.synonyms, you can see the base_object_name shows the three part name.
If you have synonyms created without a database or schema name you can use this query to find them:
SELECT name, base_object_name FROM sys.synonyms WHERE base_object_name NOT LIKE '%.%' -- one part name, no schema name, no database name OR base_object_name LIKE '%..%' -- no schema name OR (base_object_name NOT LIKE '%' + DB_NAME() +'%' AND base_object_name NOT LIKE '%..%') -- no database name GO
You can use the query above as one of your database checks or use this query as part of Policy Based Management.
This is the best practice we follow, but it does not mean that it's best for every scenario. If you only have synonyms for objects in the same database you may consider two-part names only.
Create a SSMS Synonym Template
Below is a SSSMS template that can be used to create a synonym with a three-part base object name. This will also check that the base object exists prior to creation.
--==========================================-- Create Synonym with Check (Template) --========================================== IF (SELECT OBJECT_ID('<database_name, sysname, AdventureWorks>.<schema_name, sysname, Production>.<object_name, sysname, Product>')) IS NOT NULL BEGIN CREATE SYNONYM <synonym_schema_name, sysname, dbo>.<synonym_name, sysname, sample_synonym> FOR <database_name, sysname, AdventureWorks>.<schema_name, sysname, Production>.<object_name, sysname, Product> PRINT 'Synonym <synonym_name, sysname, sample_synonym> for object <database_name, sysname, AdventureWorks>.<schema_name, sysname, Production>.<object_name, sysname, Product> Created' END ELSE PRINT 'Can not create Synonym for non-existing object' GO
To use the template, copy this code into a query window and type Ctrl+Shift+M to execute. A window will pop-up like the following where you can change the values to meet your needs and press OK to create the object.
- Read more tips about synonyms.
- If you are not familiar with SSMS Templates find out how to use them here.
- Learn more about SSMS Template Explorer here.
- Add checks to find synonyms that do not have three-part names to your scripts or to the Policy Based Management.
Last Update: 2017-05-02
About the author
View all my tips