Home > Query, SQL > BISQL # 39 :Details of Rowset functions – {SQL Server Function Part – II}

BISQL # 39 :Details of Rowset functions – {SQL Server Function Part – II}

 

Hi Friends,

Continuing from my last post on SQL server function :BISQL # 38 :Introduction to SQL Server Function – {SQL Server Function Part – I } « SQL Server Mentalist

  • Rowset functions return an object that can be used in place of a table reference in a Transact-SQL statement.
  • These functions allow you to query a remote data source that might be setup as a linked server on the current instance of SQL Server
  • All rowset functions are nondeterministic.
  • This means these functions do not always return the same results every time they are called, even with the same set of input values.
  • Rowset functions return an object that can be used as if it were a table or a view.
  • Following function under Row set function we need to discuss
                    • CONTAINSTABLE
                    • FREETEXTTABLE
                    • OPENDATASOURCE
                    • OPENQUERY
                    • OPENROWSET
                    • OPENXML

Now lets get discuss one by one in details

1.CONTAINSTABLE

Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can only be referenced in the FROM clause of a SELECT statement as if it were a regular table name.

Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.

CONTAINSTABLE should be the preferred way of querying your full-text indexes. It does involve a join, although you can also query it like this:

Syntax

SELECT * FROM CONTAINSTABLE(TableName, *,'SearchPhrase')

Use CONTAINSTABLE over CONTAINS wherever possible as it allows you to limit your results set returned by SQL FTS and orders it by relevancy. CONTAINS and CONTAINSTABLE offer better performance than FREETEXT and FREETEXTTABLE but the strictness of its matching does not offer the natural language searching that is sometimes demanded of searches

2.FREETEXTTABLE

Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can only be referenced in the FROM clause of a SELECT statement like a regular table name.

Queries using FREETEXTTABLE specify freetext-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row.

3.OPENDATASOURCE

Provides ad hoc connection information as part of a four-part object name without using a linked server name

Syntax

OPENDATASOURCE ( provider_name, init_string )

Where above keywords stands for

provider_name

Is the name registered as the PROGID of the OLE DB provider used to access the data source. provider_name is a char data type, with no default value.

init_string

Is the connection string passed to the IDataInitialize interface of the destination provider. The provider string syntax is based on keyword-value pairs separated by semicolons, such as: keyword1=value; keyword2=value.

For specific keyword-value pairs supported on the provider, see the Microsoft Data Access SDK. This documentation defines the basic syntax. The following table lists the most frequently used keywords in the init_string argument.

4.OPENQUERY

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Syntax

OPENQUERY ( linked_server ,’query’ )

Where above keywords stands for

linked_server

Is an identifier representing the name of the linked server.

query

Is the query string executed in the linked server. The maximum length of the string is 8 KB.

5.OPENROWSET

Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead. For more information, see Linking Servers. The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.

OPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset.

Syntax

OPENROWSET ( { ‘provider_name’ , { ‘datasource’ ; ‘user_id’ ; ‘password’ | ‘provider_string’ } , { [ catalog. ] [ schema. ] object | ‘query’ } | BULK ‘data_file’ , { FORMATFILE = ‘format_file_path’ [ <bulk_options> ] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } ) <bulk_options> ::= [ , CODEPAGE = { ‘ACP’ | ‘OEM’ | ‘RAW’ | ‘code_page’ } ] [ , ERRORFILE = ‘file_name’ ] [ , FIRSTROW = first_row ] [ , LASTROW = last_row ] [ , MAXERRORS = maximum_errors ] [ , ROWS_PER_BATCH = rows_per_batch ] [ , ORDER ( { column [ ASC | DESC ] } [ ,…n ] ) [ UNIQUE ]

Where above keywords stands for

‘provider_name’

Is a character string that represents the friendly name (or PROGID) of the OLE DB provider as specified in the registry. provider_name has no default value.

‘datasource’

Is a string constant that corresponds to a particular OLE DB data source. datasource is the DBPROP_INIT_DATASOURCE property to be passed to the IDBProperties interface of the provider to initialize the provider. Typically, this string includes the name of the database file, the name of a database server, or a name that the provider understands to locate the database or databases.

‘user_id’

Is a string constant that is the user name passed to the specified OLE DB provider. user_id specifies the security context for the connection and is passed in as the DBPROP_AUTH_USERID property to initialize the provider. user_id cannot be a Microsoft Windows login name.

‘password’

Is a string constant that is the user password to be passed to the OLE DB provider. password is passed in as the DBPROP_AUTH_PASSWORD property when initializing the provider. password cannot be a Microsoft Windows password.

‘provider_string’

Is a provider-specific connection string that is passed in as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider. provider_string typically encapsulates all the connection information required to initialize the provider. For a list of keywords that are recognized by the SQL Server Native Client OLE DB provider, see Initialization and Authorization Properties.

catalog

Is the name of the catalog or database in which the specified object resides.

schema

Is the name of the schema or object owner for the specified object.

object

Is the object name that uniquely identifies the object to work with.

‘query’

Is a string constant sent to and executed by the provider. The local instance of SQL Server does not process this query, but processes query results returned by the provider, a pass-through query. Pass-through queries are useful when used on providers that do not make available their tabular data through table names, but only through a command language. Pass-through queries are supported on the remote server, as long as the query provider supports the OLE DB Command object and its mandatory interfaces. For more information, see SQL Server Native Client (OLE DB) Reference.

BULK

Uses the BULK rowset provider for OPENROWSET to read data from a file. In SQL Server, OPENROWSET can read from a data file without loading the data into a target table. This lets you use OPENROWSET with a simple SELECT statement.

The arguments of the BULK option allow for significant control over where to start and end reading data, how to deal with errors, and how data is interpreted. For example, you can specify that the data file be read as a single-row, single-column rowset of type varbinary, varchar, or nvarchar. The default behavior is described in the argument descriptions that follow.

For information about how to use the BULK option, see "Remarks," later in this topic. For information about the permissions that are required by the BULK option, see "Permissions," later in this topic.

6.OPENXML

OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.

Syntax

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) [ WITH ( SchemaDeclaration | TableName ) ]

Where above keywords stands for

idoc

Is the document handle of the internal representation of an XML document. The internal representation of an XML document is created by calling sp_xml_preparedocument.

rowpattern

Is the XPath pattern used to identify the nodes (in the XML document whose handle is passed in the idoc parameter) to be processed as rows.

flags

Indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. flags is an optional input parameter, and can be one of the following values.

Todays link are follows:

Link Resource # 14 : August 8 – August 10 « Dactylonomy of Web Resource

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 40 : SQL Server Code Name “Denali” CTP3 Product Guide

· BISQL # 41 : Difference Between Char And Varchar

· BISQL # 42 : How to find count of all the records in all the table

· BISQL # 43 : Introduction to DAC – Data-Tier Application {DAC Part – I}

· BISQL # 44 : DAC-Database Project Overview {DAC Part – II}

 

Hope you will like this post on SQL Server Function Part II.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

               For More information related to BI World visit my Mentalist Blog

                               Link Resource Blog >> Daily Interesting links

                              SQL Server Mentalist >> SQL Learning Blog

                              Business Intelligence Mentalist >> BI World

                                        Connect With me on

           | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                    Copyright © 2011 – 2012 Vishal Pawar

Advertisements
Categories: Query, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: