Stuff in the FROM Clause: Interfaces

Finally wrapping up this blog series (while standing at my mobile office waiting on my daughter to finish track practice); I’ve previously discussed four different categories of database objects that can be used as the source of various SQL statements:

Base Structures

Virtual Structures

Temporary & Semi-Temporary structures

I want to wrap up with the following category: Interfaces.  Interfaces are methods by which queries can be executed on a remote server (and I use that term to mean remote to SQL Server, not necessarily remote to the host). and the results returned for consumption by a SQL statement.   There are basically three interfaces supported by SQL Server.

OPENDATASOURCE

OPENDATASOURCE is an ad-hoc method of connecting to any OLEDB provider; below is a bit of sample code from MSDN:

SELECT *
FROM OPENDATASOURCE(‘SQLNCLI’, ‘Data Source=London\Payroll;Integrated Security=SSPI’) .AdventureWorks2008R2.HumanResources.Employee

If you’re familiar at all with server naming notations for SQL Server, you can easily identify the four-part schema above (Server.Database.Schema.Object).  Note that OPENDATASOURCE is intended for limited use; heavy connections should consider using linked servers to take advantage of security management (particularly in the case of SQL Authentication).

OPENROWSET

OPENROWSET is similar to OPENDATASOURCE; it’s an ad-hoc method of calling data from an OLEDB provider.  The primary difference is that OPENROWSET supports a querystring, as well as pulling data from a single object.  This allows you a great deal of flexibility because you execute complicated queries using remote resources, and then only return the data that you need.  OPENROWSET can also be used for BULK insertion of data

OPENQUERY

OPENQUERY is intended for use with linked servers; the purpose is to allow code to be executed on a remote server first, and then the affected result sets being used on the local server.  In order to use the OPENQUERY method, you must first define a linked server, and then use a query string to do the actual heavy lifting on the other box.

Interfaces are relatively easy to understand and use, but they need to be carefully evaluated for each situation.  For example, OPENDATASOURCE allows you to quickly retrieve data from a single object on a remote server, but that means that all of the data is retrieved to the local instance.  If the remote server is across a network, then you have to deal with network latency.  OPENROWSET and OPENQUERY provide a work-around (through the use of a querystring), but the querystring can not be parameterized, so it’s a bit clunky to work with.  However, for quick and dirty movement of data from within T-SQL, interfaces can be a valuable tool at the right time.