-4

This is something I've never understood. Let's say I want a column that is storing an email address. I think,

"Ok, email addresses are usually no more than 15 characters, but I'll say 50 max characters just to play it safe."

and making that column VARCHAR(50). Of course, then this means that I have to create extra code, possibly both client- and server-side validation of entries to that column.

That brings up the question of Why not just use NVARCHAR all the time except in those rare circumstances where the logic of my application dicates a fixed or maximum length. From what I understand, if I create a VARCHAR(50) and none of the entries are more than 25 characters, that does not mean that 50% of the space is wasted, as the database knows how to optimize everything.

Again, that brings up the question of why not just use NVARCHAR.

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
user5648283
  • 5,913
  • 4
  • 22
  • 32
  • there are a lot of reason for this, for example the case i met is because the ETL tools don't support nvarchar, that drive me crazy and i have to use varchar to fix it – Raffaello.D.Huke Jan 27 '16 at 07:23
  • 4
    If the question is "why not `(n)varchar(max)`?", consider this - its a free sanity check. Or do you want to go to your email address column one day and find that someone has stored the complete works of Shakespeare, in the original Klingon? – Damien_The_Unbeliever Jan 27 '16 at 07:32
  • 2
    Perhaps not the point of your question, but email addresses are a good example of data which has a formal definition, including a maximum length. The Wikipedia page on email addresses is a good jumping-off point for the relevant RFCs: https://en.wikipedia.org/wiki/Email_address#Syntax – Ed Harper Jan 27 '16 at 08:09
  • 1
    Sure use nvarchar(max) everywhere. Skip data validation - that is just messy and more work for you. Never know when a user might want to store the Kuran in Presain in an address. Did you do any research on what N actually is? Did you do any research on how var allocates space? – paparazzo Jan 27 '16 at 08:43
  • @Frisbee No, I didn't do any research. – user5648283 Jan 27 '16 at 08:49
  • 3
    No kidding. The down arrow is for questions that do not show any research effort. – paparazzo Jan 27 '16 at 08:53
  • See http://stackoverflow.com/questions/2009694/is-there-an-advantage-to-varchar500-over-varchar8000 or http://stackoverflow.com/questions/13894528/does-size-of-a-varchar-column-matter-when-used-in-queries or http://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax – Liesel Jan 27 '16 at 09:01

1 Answers1

1

nvarchar itself has nothing to with "unlimited length of string" since it is just unicode version of varchar. At present time there are no reasons to use varchar (except some backward compatibility issues) and nvarchar should be preferred.

So I'm supposing you're asking why don't use nvarchar(max) everywhere which is almost unlimited (2 GByte of storage) instead of specifying nvarchar(n) for concrete columns.

There are many reasons of using nvarchar(n) instead of nvarchar(max).

For example, if your column should be included in index - it can't be nvarchar(max).

Also nvarchar(max) data internally stored differently than nvarchar(n) and somtimes it can affect performance.

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
  • 1
    "No reasons to use varchar over nvarchar" How about nvarchar(x) uses 2x + 2 bytes storage whilst varchar(x) uses x+ 2 bytes? https://msdn.microsoft.com/en-us/library/ms176089(v=sql.110).aspx and https://msdn.microsoft.com/en-AU/library/ms186939(v=sql.110).aspx – Liesel Jan 27 '16 at 09:05
  • @LesH if you're absoultely sure it will never be international symbols in your database - feel free to use varchar. – Andrey Korneyev Jan 27 '16 at 09:07