NVARCHAR(n) Vrs NVARCHAR(max)

, 2012-05-28

These datatypes particularly NVARCHAR(n), have been with us for some time now but having seen some of the confusion around when to use any of these, I feel oblige to share and infact to also learn more from anyone out there.

 

NVARCHAR(n)

This data is used for storing a variable-length unicode string. The variable, n, – for the mathematically inclined – denotes the length of the string data and this be anything between 1 and 4,000.

 

NVARCHAR(max) (Large value type.)

The “max” in this datatype denotes a maximum storage size of 2 GB. Values stored in a nvarchar(N) are physically stored in the same way. However for the nvarchar(max) datatype , the values are treated as a TEXT value thus some additional processing is required particularly when the size exceeds 8000.

 

So when do we use any of these

The choice of which of these data types to use, firstly lies with what the business requirements are. In my own experience, the nvarchar(n) data type has been the preferred option as per requirements and more importantly, because of performance issues.

Unless there is a compelling reason to use nvarchar(max), I will strongly recommend the use of nvarcha(n) as this will meet most business requirement and also the optimum performance one stands to gain from this. For instance, creating a field just to store any amount of data only for it hold 100 or even 200 characters is not elegant.

Hope you find this article useful, please feel free to add comments and let me know what you think.

cheers

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads