BUG: You may obtain incorrect results when you run a query against a character data type column in SQL Server 2005 (924946)


The information in this article applies to:

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup
  • 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:
  1. Drop the index on the computed column.
  2. Drop the computed column.
  3. 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
  4. Re-create the computed column on the substring of the character data type column.
  5. 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
Modification Type:MajorLast Reviewed:9/19/2006
Keywords:kbsql2005engine kbExpertiseAdvanced kbbug KB924946 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.