As long as the access interface supports the required ole db interface, each type of transact-SQL statements mentioned in this topic can be used.
Transact-SQL statements
The following list lists some subsets of the transact-SQL language. You can use these subsets for remote tables accessed by link server names or temporary names:
- All select statements with the standard formatSelect_listQueries in the where clause of the from clause are allowed. When
New_table_nameWhen a remote table is referenced, The into statement of the SELECT statement is not allowed.New_table_name
Clause.
- In select, insert, update, and delete statements, you cannot use a table name composed of a single part or four parts to restrict columns in a remote table. Should be in from
The alias of the remote table is specified in the clause, and the alias is applied to the specified column name.
- Unable to query includeXMLColumn tables, even if the query can access non-XMLColumn.
- Specify the large object (LOB) column in the remote table asSelect_listThe SELECT statement cannot contain order
By clause.
- The is null and is not null predicates cannot reference the lob columns in the remote table.
- When a query contains a where clause, group by all cannot be used in Distributed queries. The group by clause that does not specify all is supported.
- As long as the access interface meets the requirements of ole db for the insert statement, the insert statement can be used for remote tables. For more information, see .
- Null constants cannot be inserted through distributed queries.TimestampColumn.
- Distributed queries do not support $ identity and $ rowguidcol. Explicit values cannot be inserted into the identity column in the remote table.
- If the access interface meets the requirements of the ole db interface of the specified table, the update and delete statements can be used for the remote table. For more information, see .
- If the access interface meets the conditions for updating a remote table
Remote_tableWhere current
Cursor_nameWhen the remote table is specified, the remote table can be updated or deleted through the cursor defined in the distributed query. For more information, see .
- The readtext, writetext, and updatetext statements cannot be used for remote tables.
- If you instantiate an access interface outside the SQL server process, you cannot reference a large object data type in an update or insert operation (such
Text,NtextOrImage), The access interface option allowinprocess is
0. For more information, see .
- Data Definition Language statements (such as create, alter, or drop statements) cannot be used on linked servers ).
- Execute statements can use the at keyword to send a command to the linked server. Whether the statement can be executed depends on the RPC of the linked server.
Configuration depends on whether the linked server can execute this statement.
- Other database-level operations or statements cannot be executed on the linked server.
Other criteria
The following are some other restrictions and guidelines:
- A static or insensitive cursor can reference a remote table. If the ole db access interface meets certain requirements, the keyset
A cursor can reference a remote table. For more information about these requirements, see . Other types of cursors cannot reference remote tables.
- Stored procedures are supported only for SQL Server data sources.
- You must set the ansi_nulls and ansi_warnings options of the connection to on before the connection can perform distributed queries. For more information, see .
- To create an optimal query plan when using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users with limited permissions on any column of a table may not have sufficient permissions to obtain all useful statistics, and may receive inefficient query plans and experience poor performance. If the linked server is
For an SQL server instance, to obtain all available statistics, you must have the table or the SysAdmin fixed server role on the linked server, db_owner fixed database role, or
Members of the db_ddladmin fixed database role.
