Distributed Query linked server ad hoc computer

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.

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

Define Distributed Query and Linked Server

Linked servers allow SQL Server to access data from remote data sources. One can execute queries, perform data modifications, and also execute remote procedural calls using Linked servers. Queries executed through Linked servers are termed as distributed queries. These remote data sources are connected through an OLEDB provider. The types of distributed queries which can be processed is thus based on how the OLEDB provider was designed.
Extended and Remote stored procedure
SQL Server Extended and Remote stored procedure - Define temporary and extended stored procedure. You can find answer to this question in this series.
Primary and Unique key
Define Primary and Unique key. We have defined these two terms of database in this series.
Clustered and non clustered Index
SQL Server Index - What is index? Define its types. This is quite common question that might be asked during interview.
Post your comment