Data type mismatches causing SQL Server performance issues

I've been talking about this for years, but every once in a while I see a nose rubbed in it, and the results can be dramatic.

The SQL Server optimizer, in some versions (this is SQL 2005 SP3) will not properly resolve statistics if the data types of the declared variables & the compared columns don't match.

The solution: Match up your data types.

In the code sample below, I was looking at a query that was accessing SQL Server from Ruby--on-Rails. The ONLY difference in the code is that in the first example, the data type is being passed in as nvarchar(4000), and in the second exanmple it's varchar(255) the actual data type.

You can see that the relative plan costs are 99% to 1% (actual costs are more like a factor of 10,000).

Code (reproduced with the kind permission of my client):

declare @p0 nvarchar(4000)

SELECT this_.clinicalContactID as clinical1_138_0_,
this_.clinicalContactTypeID as clinical2_138_0_,
this_.socialSecurityNumber as socialSe3_138_0_,
this_.contactNumber as contactN4_138_0_,
this_.firstName as firstName138_0_,
this_.lastName as lastName138_0_,
this_.middleName as middleName138_0_,
this_.dateOfBirth as dateOfBi8_138_0_,
this_.age as age138_0_,
this_.gender as gender138_0_,
this_.raceTypeID as raceTypeID138_0_,
this_.maritalStatusTypeID as marital12_138_0_,
this_.createdOn as createdOn138_0_,
this_.createdBy as createdBy138_0_,
this_.lastUpdated as lastUpd15_138_0_,
this_.lastUpdatedBy as lastUpd16_138_0_
FROM ClinicalContacts this_
WHERE this_.socialSecurityNumber = @p0

Plans: