Take care when using Unicode data in your queries, as it can affect query performance.
A classic problem is related to an application passing in Unicode literals, while the column searched in the database table is non-Unicode. This, of course, may be visa-versa depending on your scenario.
Here is an example. The DB column "orgname_name" has been indexed, and is of type varchar. The code below performs OK (so we think) performing an index scan operation:
declare @myvar nvarchar(200)
set @myvar = N'Central West College of TAFE'
select * from Organisation_Name
where orgname_name = @myvar
--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([corpsys].[dbo].[Organisation_Name]))--Index Scan(OBJECT:([corpsys].[dbo].[Organisation_Name].[Organisation_Name]),WHERE:(Convert([Organisation_Name].[orgname_name])=[@myvar]))
Table 'Organisation_Name'.Scan count 1,logical reads 1145,physical reads 0,read-ahead reads 0.
If we change this around slightly, using a varchar variable instead (no explicit Unicode conversion) we see this:
declare @myvar varchar(200)
set @myvar = 'Central West College of TAFE'
select *
from Organisation_Name
where orgname_name = @myvar
--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([corpsys].[dbo].[Organisation_Name]))--Index Seek(OBJECT:([corpsys].[dbo].[Organisation_Name].[Organisation_Name_nameix]),SEEK:([Organisation_Name].[orgname_name]=[@myvar]) ORDERED FORWARD)
Here we see an INDEX SEEK lookup with a massive performance improvement:
Table 'Organisation_Name'.Scan count 1,logical reads 9,physical reads 0,read-ahead reads 0.
