In SQL Server, the variable column varchar (255) nvarchar

I am using SQL Server 2008 express, and some of our columns are defined as varchar (255). Should I convert these columns to NvarChar (255) or nvarchar (max)?

I ask that I read that nvarchar (255) for Unicode characters will actually save 1/2 the number of characters (since Unicode characters are 2 bytes), while 255 with varchar () will allow me to store 255 characters (or it's 255 - 2 for bias).

Have there been any performance gains when using nvarchar (max)?

Jds

+4
source share
2 answers

Well, not really - converting to NVarChar (255) does not reduce the number of characters stored in half - it still stores 255 characters. This is just twice as much space (510 bytes versus 255 bytes).

You have to convert to NVARCHAR - although it uses twice as much space - if you:

  • you need to support Arabic, Hebrew, Cyrillic or any East Asian language - only in Unicode can you actually capture these characters.
  • it is necessary to support other languages ​​that use the "standard" Latin alphabet, but have special characters - such as the East European (Slavic) languages ​​with their characters, such as č Δƒ Δ› - they will be stored as soon as c, a, e in varchar ()

NVarchar (max) is a great option - if you really need up to 2 GB of text. Creating all nvarchar (max) string fields is just "consistent" - a really really bad idea - you will have serious performance issues. See Remus Rusanu Article Related Articles

+12
source

You should have some rationale for each data type used.

nvarchar (255) (in SQL Server) stores 255 Unicode characters (510 bytes plus overhead).

Of course, you can store regular Unicode data encoded in UTF-8 in varchar columns - one varchar character for each byte in the source (UTF-8 will use several bytes respectively for wide characters). In this case, normal ASCII data uses only 1 byte per character, so you do not have double-byte overhead. This has many drawbacks, not least because the database can no longer help as much with collaborations and other character manipulations as the data is potentially encoded. But, as I said, this is possible.

I recommend char or varchar characters of the appropriate length for things like account numbers where the decimal value may not be used, because zero additions, license numbers, account numbers (with letters), postal codes, phone numbers, etc. These are column types that NEVER contain any wide characters and are usually limited only to rooted letters and numbers, sometimes even punctuation, and are often heavily indexed. There is absolutely no need for the overhead of extra NUL bytes for all these characters in the columns of both tables and indexes and in the working set in the database engine.

I recommend nvarchar for things like names and addresses, etc., where wide characters are possible, perhaps even when no use is expected in the near future.

I usually never use nchar - I never need shortcodes (usually where I selected the char columns) that needed wide characters.

In all cases, the use of length (or max) really needs to be fully taken into account. I would definitely not use max for names or addresses, and the overhead might be obvious when benchmarking. I have seen that casting in varchar (length) in the intermediate stages of queries dramatically improves performance.

+4
source

Source: https://habr.com/ru/post/1307703/


All Articles