I discovered one of the scariest performance problems with SQL Server I have ever seen today. This problem is so easy to over look, yet extremely detrimental to database performance.
Consider the following table.
BEGIN TRANSACTION GO CREATE TABLE dbo.Users ( id INT NOT NULL, username VARCHAR(50) NOT NULL ) GO ALTER TABLE dbo.Users ADD CONSTRAINT PK_Users_id PRIMARY KEY CLUSTERED ( id ) GO CREATE UNIQUE NONCLUSTERED INDEX IX_Users_username ON dbo.Users ( username ) GO COMMIT
Now look at the query plans of the following queries, that differ by a single character.
If you look closely, the query that uses an nvarchar parameter does an index scan while the one that users varchar does an index seek. This is very important, because an index seek is orders of magnitude faster than a scan.
The reason this occurs is because the parameter and column have different collation sets.