Microsoft KB Archive/308870

= INF: COLLATE for Computed Columns is Deterministic for UNICODE Starting with SQL Server Service Pack 2 =

Article ID: 308870

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q308870



SUMMARY
Some applications require collations for specific columns that do not match the collation of the table. However, if all of the following circumstances are true, you cannot build an index on such a column:
 * The table contains a column (Column1), which you defined to hold strings.


 * The table also has a computed column (Column2) based on Column1 using the COLLATE clause.


 * You issue a CREATE INDEX statement on Column2.

This error message occurs:

Server: Msg 1933, Level 16, State 1, Line 1 Cannot create index because the key column 'Column2' is non-deterministic or imprecise.

For additional information about the latest service pack for Microsoft SQL Server 2000, click the article number below to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



MORE INFORMATION
The SQL Server 2000 Books Online topic Creating Indexes on Computed Columns states in part:

The computed_column_expression must be deterministic. Expressions are deterministic if they always return the same result for a given set of inputs. computed_column_expression is deterministic if: All functions referenced by the expression are deterministic and precise.

The COLLATE function is non-deterministic. Therefore, any computed column built with the COLLATE function is defined as non-deterministic. COLLATE is non-deterministic because different versions of different operating systems may handle collations differently. However, for UNICODE values the collation is always handled the same way by all operating systems. Therefore, there is no reason to mark UNICODE collation operations as non-deterministic.

Starting with SQL Server 2000 Service Pack 2, an index may be built on a computed column that is based on a UNICODE data type, even if you use the COLLATE clause. The index creation fails, as expected, for non-UNICODE data types.

Additional query words: SP2 deterministic unicode data type datatype index performance widechartomultibyte

Keywords: kbinfo KB308870

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.