I've been doing a lot of DB refactoring lately and synonyms have come in incredibly useful. When I originally put in the synonyms I was thinking they would be very temporary while I refactor. Now I am thinking there might be some good reasons to keep some of these synonyms around.
Has anyone used them as full blow abstraction layer?
What are the performance costs?
Any gotchas with indexes?
Tips or Tricks?
My first question, so please be gentle.
Thanks
A synonyms in SQL Server are database objects that give an alternative name to the database objects existing locally or on the remote server. Also, they provide an abstraction layer to protect the application from any changes in the base object.
SQL Server allows you to perform different types of operations on the Synonyms, that change the data and not the schema of the object, such as SELECT, INSERT, UPDATE, DELETE or EXECUTE operations.
What is a synonym in SQL Server. In SQL Server, a synonym is an alias or alternative name for a database object such as a table, view, stored procedure, user-defined function, and sequence. A synonym provides you with many benefits if you use it properly.
Use the ALTER SYNONYM statement to modify an existing synonym. To modify a private synonym in another user's schema, you must have the CREATE ANY SYNONYM and DROP ANY SYNONYM system privileges. To modify a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM system privileges.
As a synonym is an abstraction/alternative name for an already existing database object, in the case of a table, index behaviour is identical to that of the underlying object i.e. when execution plans are generated, the same plan is generated irrespective of using the table name or corresponsing synonym.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With