CONVERT takes the column name, not a string containing the column name; your current expression tries to convert the string A.my_NvarcharColumn to an integer instead of the column content. SELECT convert (int, N’A.my_NvarcharColumn’) FROM A; should instead be SELECT convert (int, A.my_NvarcharColumn) FROM A; Simple SQLfiddle here.
Sounds to me like you plan to use the varchar(MAX) data type for its intended purpose. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other … Read more
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
Strictly speaking the MAX types will always be a bit slower than the non-MAX types, see Performance comparison of varchar(max) vs. varchar(N). But this difference is never visible in practice, where it just becomes noise in the overall performance driven by IO. Your main concern should not be performance of MAX vs. non-MAX. You should … Read more
You match the datatype with the data that will be stored in the column. By a similar argument you could say why not store all data in NVARCHAR columns, because numbers and dates can be represented as strings of digits. If the best match for the data that will be stored in the column is … Read more
“NATIONAL” in this case means characters specific to different nationalities. Far east languages especially have so many characters that one byte is not enough space to distinguish them all. So if you have an english(ascii)-only app or an english-only field, you can get away using the older CHAR and VARCHAR types, which only allow one … Read more
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
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)
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
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