Use synonym and openquery in the database

Abstract:
What if you want to query the data of another data server on one database server? What if you want to query data between different databases on the same data server? Let me introduce synonym and openquery for you.

1. What is synonym?
Synonym (Synonym) is a new feature of SQL 2005. It has been launched for several years. You can simply understand that synonym is the alias of other tables. We use the northwind database as an example. For example, let's look at the example below.

Create synonym mycustomers for MERs

Create a synonym for the MERs table, called mycustomers. You can use mycustomers as a normal table and query, update, delete, and insert it. For example
Query: Select * From mycustomers.
Insert: insert into mycustomers (customersid, companyName) values ('Tom ', 'ms ')
All operations are the same as normal tables.

2. Application of synonym
When your program is released, you suddenly find that you need to change the name of a table or field. However, your program cannot be modified. What should I do? Create synonym. Of course, in the SQL2000 era, you can use view to do this, or sprocs or UDF. However, synonym has other functions that cannot be used, that is, cross-database and cross-server.

3. Different databases of synonym on the same server
For different databases on the same server, we can use synonym to map tables, views, sprocs, and udfs in other databases to aliases in this database. In this way, you do not need to change the connection string. In the current dialog database, you can obtain data from other databases and perform, query, update, delete, and insert operations on it.
Assume that the northwind database already exists, and then create a database. Create synonym. Create synonym mycustomers for northiwind. DBO. customers in the new database.

Note that the database and table need to be written later. The DBO in the middle is the table owner.
Then, run insert into mycustomers (customersid, companyName) values ('Tom ', 'ms ')
And select * From mycustomers.

4. Different databases of synonym on different servers
Suppose we have a database server named sqltest. There is a database named northwind. We also have a local database server. It is called localtest. There is a database named northwind or something above it. Suddenly one day, in order to make the local database run faster, the local old data is moved to sqltest, and only the latest updates are saved locally. Old data is always used. How can we implement data operations between different servers? Use synonym. Create synonym mycustomers for sqltest. northiwind. DBO. Customers

In the previous example, a machine name is added. Is that simple? Right? The server does not necessarily know the user name and password. Well, yes. Register a remote server on the local server. Use sp_addmediaserver. The stored procedure is defined as follows:

sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]
Run the following script to register sqltest locally.
Exec sp_addmediaserver
@ Server = 'sqltest ',
@ Provider = 'msdasql ',
@ Provstr = 'driver = {SQL Server}; server = sqltest; uid = sa; Pwd = ;'
In this way, the remote database can be used locally.

5. Other synonym functions and UI-based synonym Creation
Synonym can not only create tables, but also create synonym for views, sprocs, and udfs.

Sql2005 server managerment studio has such an item. After selecting this item, the following screen is displayed. Add the name and select the object type. You can select view, table, sprocs, and UDF to create synonym.

6. openquery
Compared with synonym, openquery is weak. It only calls sprocs on other databases. For example

Select * From openquery ([sqltest], 'exec northwind. DBO. [MERs by city] @ param1 = n' 'London ''') Where customerid = 'arout'

This is the stored procedure of [MERs by city] In the northwind database on the sqltest server. You can
This stored procedure is obtained from the article "6" store procedure "in the advanced series of" LINQ to SQL "(5. The first parameter of openquery is the name of the data server. If it is a remote location, you also need to register it locally, which is the same as synonym. You can also write the local machine name, which becomes a local cross-database operation. In the preceding example, the statement contains London, which is the value of the stored procedure. Because it has been cited, it will be cited again under the openquery function. Therefore, it adds two ''to both ends ''. If you have any questions, see handling special characters in SQL statements and preventing SQL injection.

7. Support for synonym by using LINQ
Currently, sqlmetal and or designer cannot map synonym. However, you can manually modify dbml to achieve the ing purpose. Use of synonym in LINQ. And other tables.

Conclusion: through synonym, we can achieve data delivery between different databases and different servers. To achieve load balancing and improve efficiency.

References:
Sp_addmediaserver
Synonym in SQL Server 2005
How and why shocould I use SQL Server 2005 synonyms?
Using synonyms in SQL Server 2005