Collations in Distributed Queries
SQL Server 2005 supports multiple collations. Collations can be different for each column, and each character value has an associated collation property. SQL Server interprets the collation property of character data from a remote data source and treats it accordingly.
SQL Server uses the collation of remote data for comparison and ordering operations on character data, Unicode and non-Unicode. The collation information for remote character data is determined differently depending on whether the data source corresponds to an instance of SQL Server:
- The Microsoft SQL Native Client OLE DB Provider automatically reports the collation for each column the provider returns.
- For remote tables that are not in SQL Server but for which the collation is known to be the same as one of the collations supported by SQL Server, the administrator can specify the default collation of the OLE DB data source as part of the linked server definition. SQL Server can then use the default collation as the collation for all columns that are returned from that linked server.
After SQL Server determines the collation of a remote character column, SQL Server follows the same rules for converting, comparing, and operating on remote table columns as it does for local columns. For more information about the rules that SQL Server applies to collations and the collation names supported by SQL Server, see Working with Collations.
If the collation of the default remote database is different from the collation of the current local database, remote strings are treated as Unicode. To help make sure that strings are treated uniformly across all remote servers, do the following:
- Use the Unicode format, N**'string'**, to specify string literals in distributed queries.
- Define linked servers that have an explicit target database, instead of relying on a default remote database. This database may have a different collation.
Defining Linked Server Options
The following linked server options that are defined by using sp_serveroption control whether and how SQL Server uses collations from linked servers:
The use remote collation option specifies whether the collation of a remote column or of a local server will be used. When true, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for data sources other than SQL Server. When false, distributed queries always use the default collation of the local server instance, and collation name and the collation of remote columns are ignored.
The collation name option specifies the name of the collation used by the remote data source if use remote collation is true and the data source is not a SQL Server data source. The name must be one of the collations supported by SQL Server. Use this option when you access an OLE DB data source other than SQL Server, but whose collation matches one of the SQL Server collations. SQL Server data sources report their column collations, and collation name is ignored for linked servers that reference SQL Server data sources.
Note
Using the linked server options is the only way to enable using remote collations. Therefore, queries that are constructed that use ad hoc names provided by OPENROWSET and OPENDATASOURCE cannot use the collation information of remote character data. Additionally, all linked servers in SQL Server version 7.0 that are upgraded to SQL Server 2000 or later are set to use remote collation=false.
Summary of Option Settings
The following table summarizes how SQL Server determines the collation that is used for each column when use remote collation is set to true (or on).
| Linked server type | Collation name not set | Collation name set (to CollX) |
|---|---|---|
SQL Server |
Actual collation of the remote column. |
Actual collation of the remote column. |
Others |
Default collation of local instance of SQL Server. |
CollX |
The following table summarizes how SQL Server determines the collation that is used for each column when use remote collation is set to false (or off).
| Linked server type | Collation name not set | Collation name set (to CollX) |
|---|---|---|
SQL Server |
Default collation of local instance of SQL Server. |
Default collation of local instance of SQL Server. |
Others |
Default collation of local instance of SQL Server. |
Default collation of local instance of SQL Server. |
See Also
Concepts
Other Resources
Using SQL Collations
Collation Options and International Support