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

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.