SQL Server distributed queries

Send request to get interview tips in PDF format Personal Interview - Answers to frequently asked questions Tips to earn promotions and perks You can also get your answers published How to write a good CV? How to write a good Cover Letter? Tips to become boss's favorite CareerRide is hiring

SQL Server distributed queries interview questions

---

<<Previous  Next>>

SQL Server distributed queries - Nov 20, 2008 at 18:00 PM by Rajmeet Ghai 

Define 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.

Answer
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

<<Previous  Next>>