7

I'm working on some SQL Server 2012 tables to which long character strings will be imported. From my research I should use nvarchar(). However there is a lot of talk about possible serious performance problems if you use nvarchar(max), e.g. in Are there any disadvantages to always using nvarchar(MAX)?. I also see that the actual maximum number of characters for the nvarchar(max) type is 4000.

It sounds like explicitly setting the length, even if that length is equal to or nearly the maximum (maybe 3999?) could potentially avoid the most serious problems. But that doesn't make sense to me; shouldn't explicitly setting a value end up with the same result as implicitly setting the same value?

So which is the case? Is nvarchar(4000) effectively the same as nvarchar(max), or is there a real difference between the two?

Edit: The questions referenced in the comments answer the general question of specifying a length vs. setting to max. But what happens if you set to 4000? Does max actually allow more than 4000 characters? If not, why not always set to 4000 instead of max?

Rominus
  • 1,181
  • 2
  • 14
  • 29
  • 1
    This is answered here: https://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax. With regards to the different sources you read, those max figures are for two different data types - varchar(8000) and nvarchar(4000). – Chris Mack Sep 14 '17 at 16:03
  • 1
    @NisargShah that answer talks about the differences between varchar(255) and nvarchar(max). My question is about the differences between different values for the nvarchar() type. (and it's the question I reference above) – Rominus Sep 14 '17 at 16:07
  • 1
    https://stackoverflow.com/questions/20229441/implications-of-nvarchar-50-vs-nvarchar-max instead. No indexes on max and overhead on max when > 4000. – xQbert Sep 14 '17 at 16:12
  • @Rominus [VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored.](https://stackoverflow.com/a/148465/5894241) – Nisarg Shah Sep 14 '17 at 16:13
  • 1
    https://stackoverflow.com/questions/11131958/what-is-the-maximum-characters-for-the-nvarcharmax – HLGEM Sep 14 '17 at 16:41
  • @HLGEM thank you, that question does actually answer mine, unlike the others linked above – Rominus Sep 14 '17 at 16:43

0 Answers0