Query on char with selector with N prefix is very slow I have a table with a CHAR(36) "id" column, which is a clustered primary key. It has multiple other columns, including an "imdbId" column, which is an INT.

When I update using

UPDATE Movies SET imdbId=12 WHERE id = N'012e1892-872e-4bb0-804b-f70ff59689ae';


the query takes forever. And I don't really understand why. Can't SQL Server convert the Unicode string (as it does not contain special characters) and then use the index?

If I remove the N prefix, the query returns instantly.

The problem is, of course, that an existing application does all of its queries with the N prefix. Is converting my "id" column to NCHAR my best option?
It's bad enough that they are using a GUID as a Clustered PK, but they had to make it worse by making it a string and then doing a case-insensitive, locale-aware comparison? Amazing!

Well, if you see that huge of a difference between passing in a string without the N and with the N, then that indicates that the Collation of the silly CHAR(36) column is a SQL Server Collation (one starting with SQL_), most likely SQL_Latin1_General_CP1_CI_AS (the default for US installs).

But you are in luck. There are three options that should help greatly, given that you cannot change the queries themselves:


Ideally: Change the datatype of the id column to be UNIQUEIDENTIFIER: this will be faster not only because it will convert the N'xxx-xx-xx... value into a GUID first and then do a binary comparison (due to Data Type Precedence), but each row will be something like 22 bytes smaller (36 bytes for VARCHAR(36) down to 16 bytes for UNIQUEIDENTIFIER, minus another 2 or so for getting rid of a variable-length column).
If you can't do #1: Change the Collation of the id column to a Windows Collation, and preferably a binary one (ending in _BIN2), such as Latin1_General_100_BIN2. This won't save any space, but won't be nearly as expensive to convert the incoming NVARCHAR literal to CHAR(36) (or vice-versa), AND will be a binary comparison (much faster than case-insensitive, locale-aware) since it is a GUID and not subject to linguistic rules.
If you can't even do #2: Change the Collation of the id column to a Windows Collation that isn't a binary one, most likely Latin1_General_100_CI_AS. This doesn't save space or even do a binary comparison, but does allow for sloppy coding / ad-hoc queries with mixed-case values AND is still much faster than the conversion used by the SQL Server Collation.


You can see this behavior yourself via the following test:

SET NOCOUNT ON;

CREATE TABLE dbo.GuidPkAsUI (
  ID UNIQUEIDENTIFIER NOT NULL CONSTRAINT [PK_GuidPkAsUI] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsUI_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCci (
  ID CHAR(36) COLLATE Latin1_General_100_CI_AS NOT NULL CONSTRAINT [PK_GuidPkAsCHARci] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARci_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCbin (
  ID CHAR(36) COLLATE Latin1_General_100_BIN2 NOT NULL CONSTRAINT [PK_GuidPkAsCHARbin] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARbin_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCsql (
  ID CHAR(36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [PK_GuidPkAsCHARsql] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARsql_InsertTime] DEFAULT (GETDATE())
);

INSERT INTO dbo.GuidPkAsUI ([ID])
    SELECT NEWID()
    FROM   master.sys.objects
    CROSS APPLY master.sys.all_columns;

INSERT INTO dbo.GuidPkAsVCci ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;

INSERT INTO dbo.GuidPkAsVCbin ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;

INSERT INTO dbo.GuidPkAsVCsql ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;


SELECT * FROM dbo.GuidPkAsUI;
-- Pick a value from half-way down and paste into
-- the 4 queries below


SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsUI
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3


SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCci
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3


SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCbin
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3


SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCsql
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 7157 (yikes!)