Monday, May 7, 2012

SSRS Parameters are Forced Unicode

While doing some performance tuning on a report query today, I ran into the issue of SSRS sending all strings to a SQL Server database as unicode. Here is a snippet of the query I was working with:
or tbl.Field in (N''0100'',N''1002'',N''1201'',N''1202''....
This snippet came right out of profiler with no editing...

The N in front of each string forces SQL Server to treat it as unicode, as an NVARCHAR string. The issue with this is that my tbl.Field column is a varchar. So no indexes would ever be used for this query, it was always a clustered index scan.

My workaround was to add an nvarchar column, using a trigger to popuate it with the nvarchar version of the varchar field. Messy, but once I did that, and indexed it, the query performed fine.

I have opened up a Connect suggestion, so please visit that and vote for it. This situation should not exist.

https://connect.microsoft.com/SQLServer/feedback/details/740975/ssrs-sends-all-strings-to-sql-server-as-unicode

peace