7

I came across a question while buidling schema for my application.

When to use varchar(max) and nvarchar(max). I mean the exact use cases where it should be applied. I have surfed in net too but I was able to get the exact answer.

Could anyone suggest some exact usecase.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
selva
  • 184
  • 2
  • 3
  • 16
  • Say for example If i give varchar(max) and when my max data will 1000 characters and not beyond that(where i should give varchar(1000)). Is there will be memory or performance degradation as i give varchar(max) instead of 1000 Characters – selva Jun 16 '15 at 06:53

4 Answers4

20

This is for Microsoft SQL Server:

NVARCHAR is Unicode - 2 bytes per character, therefore max. of 1 billion characters; will handle East Asian, Arabic, Hebrew, Cyrillic etc. characters just fine.

VARCHAR is non-Unicode - 1 byte per character, max. capacity is 2 billion characters, but limited to the character set you're SQL Server is using, basically - no support for those languages mentioned before

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • You can't say 2 bytes per character unless dbms is specified! Implementation defined... – jarlh Jun 16 '15 at 06:37
  • @jarlh: It's not implementation defined as `VARCHAR(MAX)` is SQL Server syntax, in Standard SQL there's a `CLOB`, `CHARACTER LARGE OBJECT` instead. – dnoeth Jun 16 '15 at 07:08
  • Sorry, I didn't realize it's SQL Server specific, thought other products have it too. (Why has the question no SQL Server tag...) – jarlh Jun 16 '15 at 07:09
2

Varchar(max)

Varchar fields can be of any size up to a limit, which varies by databases: an Oracle 9i database has a limit of 4000 bytes, a MySQL database has a limit of 65,535 bytes (for the entire row) and Microsoft SQL Server 2005 has a limit of 8000 characters (unless varchar(max) is used, which has a maximum storage capacity ...

nvarchar(max)

abcdefg n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

Community
  • 1
  • 1
Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32
0

You should use nvarchar if you have to store unicode char. If you only store non-unicode-char you can use varchar.

Jens
  • 67,715
  • 15
  • 98
  • 113
0

nvarchar is for unicode data, whilst varchar is uses only up to 8-bit codepage. Use cases: varchar is good when you're dealing with Latin letters in general, so let's say you have a blog and the language used to write to this blog is English, at this case varchar is a good option. nvarchar is good when you're building a multilingual application, so you're using characters like Mandarin, Arabic ..etc.

Hope this helps.

Hassan Habib
  • 56
  • 5