SQL Server distributed queries

Answer
Distributed queries access data from multiple heterogeneous sources. These data sources may or may not be stored on the same computer. Distributed queries are most useful when there are multiple databases of varied nature. Hence, distributed queries can be executed over these databases in a secure manner.

Describe how Linked server is used to excess external data.

Answer
A linked server can be considered as another SQL server database running elsewhere. It can be a OLEDB or ODBC data source. They help in addressing distributed queries.

OPENQUERY function is used for such purposes. This function I is used to execute the specified query on the linked sever. OPENQUERY can be referenced in from the FROM clause just like a table name.

Example:
SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

Describe how OPENQUERY function is used to excess external data.

Answer
OPENQUERY is used to execute the specified query on the linked sever.

OPENQUERY can be referenced in from the FROM clause just like a table name.

Syntax:

OPENQUERY ( linked_server ,'query' )

OPENQUERY does not accept variables for its arguments.

Example:
SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name, id FROM joe.titles')

Describe how OPENROWSET and OPENDATASOURCE function is used to access external data.

OPENROWSET: Includes all connection information that is required to access remote data from an OLE DB data source. It can be references from a FROM clause and is used to access the tables on a linked server. It can also be referenced as a target table of INSERT, DELETE, and UPDATE statements.

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 }
} )

Example
SELECT * FROM OPENROWSET('SQLNCLI',Server=Brighton1;Trusted_Connection=yes;', 'SELECT sal, employee_name FROM Company.employee.ID')

OPENDATASOURCE: Provides ad hoc connection information without a linked server name.

Syntax:
OPENDATASOURCE ( provider_name, init_string )

Example: The example cretes a ad hoc connection to the payroll instance of a SQL server in US. It queries the Company.HumanResources.Employee table.

SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=US\Payroll;Integrated Security=SSPI') . Company.HumanResources.Employee

SQL Server Distributed Queries

Distributed queries can access data from different data sources.
These sources can reside on the same server or a different server.
Theycan querymultiple databases. .