By looking at the query plan we can see that the internal GetRangeThroughConvert method is used to get the search range in the new range seek optimizations in SQL Server 2005. Of course I have no idea what this method does internally but my guess would be that it uses column statistics to plot the correct search range. You can read more about that in this this post by Craig Freedman who was nice enough to help me confirm that this issue is actually a bug.

This bug is apparently fixed in SQL Server 2008, but I haven't tested this.

Feedback

Hi,ProjectNumber is defined as VARCHAR(12) but you refer to it by using unicode notation prefixing the search argument with N literal. If you either omit the 'N' or define ProjectNumber collumn as NVARCHAR(12) you will get the expected behavior.

SET ANSI_PADDING OFF prior to CREATE INDEX statement should be changed to SET ANSI_PADDING ON as well, otherwise SQL Server throws an error on index creation.

I'm using SQL Server 2005 SP2 with Cumulative Update Fix 7 (9.00.3239).I tend to think that it is rather unpredictable behavior than bug.