Why there is no full-scan (On SQL 2008 R2 and 2012)?
DROP TABLE dbo.TestTable GO CREATE TABLE dbo.TestTable ( TestTableID INT IDENTITY PRIMARY KEY, VeryRandomText VarChar(50), VeryRandomText2 VarChar(50) ) Go Set NoCount ON Declare @i int Set @i = 0 While @i < 10000 Begin Insert Into dbo.TestTable(VeryRandomText, VeryRandomText2) Values(Cast(Rand()*10000000 as VarChar(50)), Cast(Rand()*10000000 as VarChar(50))); Set @i = @i + 1; End Go CREATE Index IX_VeryRandomText On dbo.TestTable ( VeryRandomText ) Go
When execute query:
Select * From dbo.TestTable Where VeryRandomText = N'111' -- bad
Get warning (as expected, because comparing nchar data to varchar column):
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[DemoDatabase].[dbo].[TestTable].[VeryRandomText],0)" />
But then i see execution plan, and i can see, that it is not using full-scan as i would expect, but index seek instead.
Of course, this is kind of good, because in this particular case execution is way faster than if there would be full scan.
But i can not understand how SQL server came to decision to make this plan.
Also- if the server collation would be Windows collations on server level and SQL Server collation database level, then it would cause full scan on the same query.