Methods for querying or performing additional database operations in SQL 2005

Original: Method of querying or performing additional database operations in SQL 2005

Summary:
If you want to query the data of another data server on one database server, how do you do it? What if you want to query data between different databases on the same data server?

1.Synonym

2.openquery

3.Linked Servers

Server object-->linked servers--> servertype Select SQL Server, select "be made using this security context" in security
Enter your user name and password, using:
SELECT * FROM [Galaxy]. DataWarehouse.dbo.Fact_Job_Statistics WHERE ...

[Go] database using synonym and OpenQuery

October 23, 2007 0:45:32 Released: Yaosansi
Http://www.cnblogs.com/atomplus/archive/2009/04/28/1444988.html

1, what is synonym
Synonym (synonym) is a new feature of SQL 2005. has been introduced for several years. You can simply understand the alias of synonym for other tables. Let's use the Northwind database as an example. For example, look at the following example

Createsynonym Mycustomers forCustomersCreate a synonym for the Customers table, called Mycustomers. You can think of this mycustomers as a normal table that can be queried, updated, deleted, and inserted. Like what
Query: Select * from Mycustomers.
Insert: INSERT INTO mycustomers (CUSTOMERSID, CompanyName) VALUES (' Tom ', ' MS ')
All operations are not distinguished from ordinary tables.

2, application of synonym
When your program is published, you suddenly find that you need to change a table name, or field name. And your program is impossible to modify. At this time, how to do? Then create a synonym. Of course, in the sql2000 era, you can use view to do this thing, or SPROCs or UDF. But synonym has other functions that are not available, that is, cross-database, cross-server.

3,synonym different databases on the same server
For different databases on the same server, we can use synonym to map aliases in this database to tables or view or SPROCs and UDFs in other databases. This allows you to get data from other databases without changing the connection string, and in the case of the current conversation database, and to perform, query, update, delete, and insert work on it.
Assume that the Northwind database already exists, and then build a database. We create the synonym of the Customer table on the new database.

Createsynonym Mycustomers forNorthiwind.dbo.CustomersIt should be noted that the database, the table, and the middle dbo are the owner of the table.
Then, run Insert into mycustomers (Customersid, CompanyName) VALUES (' Tom ', ' MS ')
and select * from Mycustomers. See if it's really like a regular watch.

4, synonym different databases on different servers
Suppose we have a database server called Sqltest. There's a database called Northwind. We also have a local database server. Called Localtest. It has a database called Northwind or something. Suddenly one day, in order to make the local database run faster, the local old data is moved to sqltest up, local only save the most recently updated. The old data will always be used, how to implement the data between different servers? Then use synonym. As follows

Createsynonym Mycustomers forSqlTest.Northiwind.dbo.CustomersOn the basis of the previous example, a machine name was added. Is that so simple? No way? That server does not necessarily know the user name and password. Well, yes, also on the local server, register the remote server. With sp_addlinkedserver, the secondary 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‘ ]
You can run the following script to register the sqltest with the local
EXEC sp_addlinkedserver
@server = ' Sqltest ',
@provider = ' Msdasql ',
@provstr = ' Driver={sql Server}; Server=sqltest; Uid=sa; pwd=; '
This allows the remote database to be used as a local.

Other features of 5,synonym and creating synonym with UI
Synonym can not only create a table, but also create a corresponding synonym for View,sprocs, as well as UDFs.

Sql2005 Server Managerment Studio has such an option, the following screen appears. Add a name, etc., select the type of object, and you can choose to create synonym for View,table, SPROCs, and UDF.


6, OpenQuery
The function is weaker relative to the synonym,openquery. It only calls SPROCs on other databases. For example,

Select* fromOpenQuery([Sqltest], 'exec northwind.dbo. [Customers by city] @param1 =n"'London" ") whereCustomerID='Arout'

This is the stored procedure under the Northwind database on the Execution sqltest server, [Customers by City]. You can be in
LINQ to SQL Advanced Series (v) Store procedure in this article, you get the stored procedure. OPENQUERY its first parameter is the name of the data server. If it is offsite, you also need to register it locally, and synonym the same. You can also write the local machine name, which is the local cross-database operation. In the above example, the statement has London, which is the value of the incoming stored procedure. Because it has been ' cited, under the OPENQUERY function, but also ' cited. So, it adds two "at both ends. If in doubt, see Handling special characters in SQL statements and preventing SQL injections

7, Linq support for synonym
Currently SQLMetal and or designer cannot map synonym. However, the user can still manually modify the DBML to achieve the purpose of mapping. Synonym for use in LINQ. There is no difference from other tables.

Conclusion: Through synonym, we can realize the data shunt between different databases and different servers. To achieve the purpose of balancing the load, to improve efficiency.

Methods for querying or performing additional database operations in SQL 2005