Article ID: 924946
Article Last Modified on 2/7/2007
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Service Pack 1
SYMPTOMS
In Microsoft SQL Server 2005, you may obtain incorrect results when you run a query against a character data type column. This problem occurs if the following conditions are true:
- The character data type column uses the nvarchar(max) data type together with a binary (BIN) collation.
- A computed column is defined on the character data type column.
- The computed column uses a substring function. For example, the computed column uses the SUBSTR function or the LEFT function.
- The computed column is indexed.
- The query plan uses the index on the computed column.
Note This problem does not occur if the character data type column uses one of the following data types:
- The varbinary data type
- The varchar(max) data type together with a BIN collation or a BIN2 collation
- The nvarchar(max) data type together with a BIN2 collation
CAUSE
This problem occurs because SQL Server 2005 generates an incorrect additional predicate under the conditions that are mentioned in the "Symptoms" section. This additional predicate is intended to make it easier to match the index on the computed column.
Note SQL Server 2005 generates a correct predicate if the character data type column uses one of the following data types:
- The varbinary data type
- The varchar(max) data type together with a BIN collation or a BIN2 collation
- The nvarchar(max) data type together with a BIN2 collation
In this case, you obtain correct results.
WORKAROUND
To work around this problem, follow these steps:
- Drop the index on the computed column.
- Drop the computed column.
- Change the character data type column to use one of the following data types:
- The varbinary data type
- The varchar(max) data type together with a BIN collation or a BIN2 collation
- The nvarchar(max) data type together with a BIN2 collation
- Re-create the computed column on the substring of the character data type column.
- Rebuild the index on the computed column.
For example, run the following statements in SQL Server 2005.
-- Start from the same schema in tempdb as in the "Steps to reproduce the problem" section. drop index t.t_mystr_pref_idx go alter table t drop column mystr_pref go alter table t alter column mystr nvarchar(max) collate Latin1_General_Bin2 go alter table t add mystr_pref as convert(nvarchar(2), substring(mystr, 1, 2)) go create index t_mystr_pref_idx on t(mystr_pref) include (mystr) go -- Verify workaround: The statement now returns one row when the statement uses the index on the computed column. declare @v nvarchar(20) set @v = N'a' + nchar(0x000D) select mystr from t where mystr > @v go -- Similarly, the statement returns one row when the statement does not use the index on the computed column. declare @v nvarchar(20) set @v = N'a' + nchar(0x000D) select mystr from t with (index = 0) where mystr > @v go
Note These statements are based on the statements in the "Steps to reproduce the problem" section.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
The following scenario is common. You create an index on a computed column. The computed column is defined as the substring of an nvarchar(max) data type column. In this scenario, SQL Server 2005 uses the index and the additional predicate to improve query performance.
Steps to reproduce the problem
Run the following statements in SQL Server 2005.
use tempdb go create table t ( pk int, mystr nvarchar(max) collate Latin1_General_Bin, mystr_pref as convert(nvarchar(2), substring(mystr, 1, 2))) go create index t_mystr_pref_idx on t(mystr_pref) include (mystr) go insert t values (1, N'a a') go -- The following statement uses the index on the computed column and returns zero rows. declare @v nvarchar(20) set @v = N'a' + nchar(0x000D) select mystr from t where mystr > @v go -- The same statement returns one row when the statement does not use the index on the computed column. declare @v nvarchar(20) set @v = N'a' + nchar(0x000D) select mystr from t with (index = 0) where mystr > @v go
Keywords: kbtshoot kbbug kbexpertiseadvanced kbsql2005engine KB924946