Truncated varchars in Stored Procedures

[mrcook]

SQL statements are generated using stored procedures, but SQL Server will truncate varchar values to the limit as given in the column. As an example, I create a migration like so:

create_table :users do |t|
  t.string :code, unique: true, limit: 5
end

And then then create a new record giving code the value ABCDE.

A typical SQL query generated by the gem would be:

EXEC sp_executesql N'SELECT  [users].* FROM [users] WHERE [users].[code] = @0  ORDER BY [users].[id] ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY', N'@0 nvarchar(5)', @0 = N'ABCDEFGH'

If I execute this directly on MSSQL the value ABCDEFGH would be truncated down to ABCDE because of the nvarchar(5) casting, and so we get the record back. This happens while fetching records as well as inserting, and likely other actions too. ActiveRecord validations will not catch these behaviours.

Here's an SO post with some more details on this known MSSQL behaviour: http://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures

A proposed solution is to use nvarchar(max), even though there would be a small performance hit.

If I hack the gem to make this change (.sub(/nvarchar\(\d+\)/, 'nvarchar(max)')) then an insert will no longer truncate (desired behaviour), a .where would fail to return the record (as expected), and ActiveRecord validations on length would kick in.

[metaskills]

Thanks. I'll take a look at this once (perf too) once I get the Rails v5 work done.