This is how you explicitly set nvarchar(max): cmd.Parameters.Add(“@JobNumbers”, SqlDbType.NVarChar, -1); If you’re really concerned with performance you might want to consider passing a table of integers: https://stackoverflow.com/a/10779593/465509
The advantages are that you can use functions like LEN and LEFT on nvarchar(max) and you cannot do that against ntext and text. It is also easier to work with nvarchar(max) than text where you had to use WRITETEXT and UPDATETEXT. Also, text, ntext, etc., are being deprecated (http://msdn.microsoft.com/en-us/library/ms187993.aspx)
The real reason you want to use NVARCHAR is when you have different languages in the same column, you need to address the columns in T-SQL without decoding, you want to be able to see the data “natively” in SSMS, or you want to standardize on Unicode. If you treat the database as dumb storage, … Read more
You have declared this as nvarchar(max) which allows 2GB of data so it will store 2GB. What is happening: The datatype is not yet nvarchar(max) until assignment to @sql1 Before that, it’s a collection of strings, each less than 4000 (constants) You are concatenating short constants with short variables (short = < 4000) So you … Read more
Disk space is not the issue… but memory and performance will be. Double the page reads, double index size, strange LIKE and = constant behaviour etc Do you need to store Chinese etc script? Yes or no… And from MS BOL “Storage and Performance Effects of Unicode” Edit: Recent SO question highlighting how bad nvarchar … Read more
TLDR; This is not a documented/supported approach for concatenating strings across rows. It sometimes works but also sometimes fails as it depends what execution plan you get. Instead use one of the following guaranteed approaches SQL Server 2017+ SELECT @a = STRING_AGG([msg], ”) WITHIN GROUP (ORDER BY [priority] ASC) FROM bla where autofix = 0 … Read more
An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk … Read more