Thursday, 27 December 2012

Rowset functions in SQL Server 2012


SQL Server provides many built-in functions. Let’s see one of the types called Rowset functions. We can also create our own user defined functions in SQL Server based on the end-user requirement.

Rowset functions can be used to access remote data through OLEDB provider. It can be used like table reference as in T-SQL Statements. It does not always return the same result set all the time even though it has same input values. That’s the reason rowset function is called as non-deterministic functions.

It is categorized into 4 types.

It is used to access data from remote OLEDB data sources.
2.   OPENQUERY
It executes the given query through specified Linked server which is an OLE DB data source.
3.   OPENROWSET
It is used to access data from remote OLEDB data sources.
4.   OPENXML
It is used to provide a rowset view for an XML document.

We will see detailed information about Rowset functions in the upcoming articles.