|
By Nishant Kumar
Define Distributed Query and Linked Server?
Distributed Query
-
Distributed Query is a query which can retrieve data from multiple data sources
including distributed data.
-
SQL Server supports distributed query through the use of OLE DB provider.
-
OLE DB provider exposes data in the row sets which can be used in SQL
Statement.
|
SQL Server can use distributed query in the SQL Statement using
Linked Server
It is the virtual server that is created to access OLE DB data source.
It includes all the information needed to access OLE DB data source.
Linked server definition contains all the information needed to locate OLE DB
data source.
You can join remote data and local data using Linked Server.
Ad doc computer Name
Ad doc computer Name is used with infrequent distributed queries that are not
defined with linked server name.
Distributed Queries - October 24, 2008 at 18:10 pm
by Rajmeet Ghai
What is Distributed Queries?
Distributed queries can access data from different data sources. These sources
can reside on the same server or a different server. This means that
distributed queries can query multiple databases.
What is a linked server?
A linked server allows remote access. They have the ability to issue distributed
queries, update, commands, and transactions across different data sources. A
linked server has an OLE DB provider and data source.
Explain OPENQUERY function and OPENROWSET function.
OPENQUERY: - Used to execute the query passed on the linked server.
Syntax: OPENQUERY (Linked_server_identifier, query). It can also be refernced
from a FROM clause of selecte query.
e.g. Select * from OPENQUERY (Oracleserver, ‘select fname, FROM Employee);
OPENROWSET: - Used to access tables in a linked server. It can be used one time
for accessing remote data by using OLE DB. It includes all the information
required to access the remote data.
Syntax:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [
schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [
]
| SINGLE_BLOB |
SINGLE_CLOB | SINGLE_NCLOB }
} )
It is the process of organizing data into related table. To normalize database,
we divide database into tables.....
SQL Server Provides three types of Transact-SQL statements namely DDL, DCL, and
DML....
The Bulk Copy is a command utility that transfer SQL data to or from a data
file.
This utility mostly used to transfer huge data to SQL server from other
database....
The column or columns of the table whose value uniquely identifies each row in
the table is called primary key. You can define column as primary key using
primary key constraint while you create table.....
A transaction is a set of operations that works as a single
unit. The ransactions can be categorized into explicit, autocommit, and
implicit....
|