Where to Use Synonyms
- In a scenario where we have lengthy names and want to keep an alias with a short name.
- It enhances security since it helps hide the schema details.
- In case we want to avoid hardcoding by using different development environments
- To enable dynamic schema and server changes, the synonym pointing will change when the schema is changed.
- To enhance the readability by providing meaningful synonyms.
Synonyms in SQL Server
The SYSNONYM is the alternative name or the alias name for the database objects so the same database object can be referred by different names without using the complex names that are defined before. The SYNONYM can be created for the tables, stored procedures, user-defined functions, or any database objects.
The SYNONYMS becomes invalid when:
- The base table is dropped.
- The name of the base table is changed.
- Creating a synonym for a synonym is not possible.
Syntax:
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR target_object
The object can be server_name. [ database_name ]. [ schema_name_2 ] .object_name or database_name. [ schema_name_2 ] .object_name or schema_name_2.object_name.