I have a table with all NVARCHAR columns and i have an procedure which manipulates that table data. But in my sp i have declared all the variables in VARCHAR data type only. Now how the execution plan will be created? after sometime i will be changing my procedure in NVARCHAR data type variables. After changing it to NVARCHAR type, will the same execution plan get executed or it will create new execution plan?? Because there is no change in any query. Just changing the data type from VARCHAR to NVARCHAR.
Fortunately for you, the table has the datatypes with the wider domain. You should see minimal changes in the query plans. If this was the other way around (table with varchar, and variables defined as nvarchar), you would have seen implicit conversions peppered in the query plan.