Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - Synonyms Tips & Tricks? [closed]

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

like image 820
Chad Grant Avatar asked Apr 28 '09 08:04

Chad Grant


People also ask

How do synonyms work in SQL Server?

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.

Can we insert data into synonyms in SQL Server?

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.

Does SQL Server have synonyms?

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.

How do you modify synonyms in SQL Server?

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.


1 Answers

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.

like image 129
John Sansom Avatar answered Oct 12 '22 06:10

John Sansom