A simple T-SQL query plan reuse question [duplicate]

Question

Even though I have parameterized this INSERT on SQL Server, it fails to reuse the plan if the length of one of the VARCHAR fields changes. Do I have any recourse short of padding the data?

The query is simple and fully parameterized:

INSERT INTO USER_LOG_DATA (POSTDT, SESSION_ID, USERID, SUBJECT, APP, ACTION_NAME, ARGS, SUMMARY, DETAIL) VALUES (@timestamp, @session, @user, @subject, @app, @action, @argsString, @summary, @detail)

However if the length of one of the strings changes, the plan is not re-used. So if the value of the @userid changes from 10 to 8, a new plan is created.

Answer 1

...it fails to reuse the plan if the length of one of the VARCHAR fields changes. Do I have any recourse short of padding the data?

This is by design, and no you should not pad the data.

The ideal solution is to declare your variable size to the same length as the data type of the column it's being inserted into. This will give you predictable plan reuse without having to send extra whitespace data.

The way you do this will vary based on how the insert is being performed. If you're using stored procedures, just update the parameters to use the same length as the columns. If you're using .NET's AddWithValue method (which is evil), use one of the APIs that allows you to set the parameter length.