When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server?

by

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, it is perfectly possible to store wide strings and different (even variable-length) encodings in VARCHAR (for instance UTF-8). The problem comes when you are attempting to encode and decode, especially if the code page is different for different rows. It also means that the SQL Server will not be able to deal with the data easily for purposes of querying within T-SQL on (potentially variably) encoded columns.

Using NVARCHAR avoids all this.

I would recommend NVARCHAR for any column which will have user-entered data in it which is relatively unconstrained.

I would recommend VARCHAR for any column which is a natural key (like a vehicle license plate, SSN, serial number, service tag, order number, airport callsign, etc) which is typically defined and constrained by a standard or legislation or convention. Also VARCHAR for user-entered, and very constrained (like a phone number) or a code (ACTIVE/CLOSED, Y/N, M/F, M/S/D/W, etc). There is absolutely no reason to use NVARCHAR for those.

So for a simple rule:

VARCHAR when guaranteed to be constrained
NVARCHAR otherwise

More Related Contents:

  1. SQL Query Where Column = ” returning Emoji characters 🎃 and 🍰
  2. What is the difference between varchar and nvarchar?
  3. nvarchar concatenation / index / nvarchar(max) inexplicable behavior
  4. What are the main performance differences between varchar and nvarchar SQL Server data types?
  5. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  6. SQL Server default character encoding
  7. determining the character set of a table / database?
  8. Changing SQL Server collation to case insensitive from case sensitive?
  9. nvarchar(max) vs NText
  10. If condition in SQL Server 2008
  11. How to get the last row from a table in SQL Server?
  12. Comma separated results in SQL
  13. How do I fix the error ‘Named Pipes Provider, error 40 – Could not open a connection to’ SQL Server’?
  14. Which are more performant, CTE or temporary tables?
  15. Preferred Method of Storing Passwords In Database
  16. Why is it considered bad practice to use cursors in SQL Server?
  17. SQL WHERE.. IN clause multiple columns
  18. How do I list all tables in all databases in SQL Server in a single result set?
  19. How do I copy SQL Azure database to my local development server?
  20. Can you call a webservice from TSQL code?
  21. How to safely use reserved SQL names?
  22. Comparing dates stored as varchar
  23. SQL Server: the maximum number of rows in table [closed]
  24. SQL Server 2008 Vertical data to Horizontal
  25. SSRS report definition is newer than Server
  26. Joining multiple tables returns NULL value
  27. Which join syntax is better?
  28. How to maintain the order of insertion in SQL Server
  29. How to convert text column to datetime in SQL
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment