title: SQL Server uses high CPU when searching inside nvarchar strings
body: <p>Check out the following example. It shows that searching within a unicode string (nvarchar) is almost eight times as bad as searching within a varchar string. And on par with implicit conversions. Looking for an explanation for this. Or a way to search within nvarchar strings more efficiently.</p>
  
  <pre><code>use tempdb
  create table test
  (
      testid int identity primary key,
      v varchar(36),
      nv nvarchar(36),
      filler char(500)
  )
  go
  
  set nocount on
  set statistics time off
  insert test (v, nv)
  select CAST (newid() as varchar(36)),
      CAST (newid() as nvarchar(36))
  go 1000000
  
  set statistics time on
  -- search utf8 string
  select COUNT(1) from test where v like '%abcd%' option (maxdop 1)
  -- CPU time = 906 ms,  elapsed time = 911 ms.
  
  -- search utf8 string using unicode (uses convert_implicit)
  select COUNT(1) from test where v like N'%abcd%' option (maxdop 1)
  -- CPU time = 6969 ms,  elapsed time = 6970 ms.
  
  -- search unicode string
  select COUNT(1) from test where nv like N'%abcd%' option (maxdop 1)
  -- CPU time = 6844 ms,  elapsed time = 6911 ms.
  </code></pre>