A perfect storm of unusual problems caused a client to ask, “What if we just used NVARCHAR(4000) as the default datatype parameter for any query, regardless of what datatype the table has in it – like numbers or dates?”
It actually works pretty well in most situations, believe it or not.
The Users table of the Stack Overflow database is helpful for demoing this because it has strings, integers, and dates. We’ll create a few indexes:
DropIndexes; GO CREATE INDEX DisplayName_NVCH ON dbo.Users(DisplayName); CREATE INDEX Reputation_INT ON dbo.Users(Reputation); CREATE INDEX LastAccessDate_DATE ON dbo.Users(LastAccessDate);
And then we’ll create a stored procedure with a