On a stored procedure (Sql 2005) parameter should I use @Name nvarchar(255) or @Name nvarchar(Max)

Question

The procedure will return from a table where name's width is set to 255.

So is there any advantage in using @Name nvarchar(255) over @Name nvarchar(Max)

Answer 1

In this case the gain that you would get for using @Name nvarchar(max) is if you ever increase the size of the table width your sproc won't be truncating anything. Other than that there is no advantage

Answer 2

nvarchar(max) is very convenient for when your application needs to handle longer strings. But you should also be very careful, as nvarchar(max) will often try to infer a size during calculations. If your data is definitely restricted to 255, then it's probably best to use that. SQL will still store that outside the page if required, but will do slightly less size inferring.

Rob