Usage of synonym synonym in SQL Server

Source: Internet
Author: User

It used to be that the synonym (synonym) in SQL Server was useless, so it never looked up its syntax format. Today, there is a problem, with synonym to solve the better. The problem is that I use multiple databases in my system, and because some of the data in each database needs to be synchronized, I add triggers to the tables in one of those databases to synchronize updates to the data in other databases. Examples are as follows:
There are two databases A and B, with tables A1 and B1, and then I create a trigger in table A1 to update the data in the table B1, the trigger is probably as follows:

Create TRIGGER TU_A1 after update
Update T2 set t2. field =t1. field
From inserted t1,b. B1 T2 where T1. field =t2. field

Anyway, I used a B in the A1 trigger. This is the way to reference objects in database B.

And then the question came, and today the boss says to install multiple systems on the same server at the same time. Then you need to copy database A and database B multiple copies, of course, the database name can not be called A and B, may be called A1 and B1. The trigger in A1 cannot update the data in B1 because the trigger in A1 is using B. This way to refer to the. In this case, you must put all B.. All replaced by B1 ... In this way, if there are more places to cite, it is a disaster.

The use of synonyms synonym can solve this problem well, first of all about the role of synonyms (synonym).
synonym : Defines a string name that is used instead of a meaningful reference object.

In the example above, I can use the BB1 string to substitute B. B1, then I'm going to operate B. B1 This table, you can directly manipulate the BB1, with the same effect as the former. Then, when deploying multiple sets of systems, just modify the reference to this synonym in the new database, and the trigger does not need to be modified at all.

The following is an example of a SQL statement for "Create, delete, reference" synonyms, note that synonyms do not provide the functionality to modify, so once created, it can only be removed and rebuilt, and cannot be modified.

Create synonyms
Create synonym Bb1
From B.. B1

Using synonyms
INSERT into BBL (...) for values (...).

Reference synonyms

Delete synonyms
Drop synonym Bb1

Query all synonyms
SELECT * FROM sys.synonyms

Note: Synonym is only available in SqlServer2005 and later versions. Ong>.


Usage of synonym synonym in SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.