How much length can NVARCHAR(MAX) store?

Question

I have a table where I am capturing the response returned by an API. The response is an exception coming from API. The response is essentially a Fault Exception.

The Response column data type is NVARCHAR(MAX). I did a query on the length of the response.

SELECT Len(Request) as [Request Length], Len(Reponse) as [Response length] from SpiderDealioSvc_ErrorLog.

The result of the query is:-

Request Length  Response length
3152    212
3152    212
2953    5101
2953    5101
2953    5101

My question is suppose the Response is 20000, would NVARCHAR(MAX) take it or would there an error or issue. What would be the MAX Length that an NVARCHAR could take.

I am also pasting the Response string:-

{
  "Fault": {
    "faultcode": "a:DeserializationFailed",
    "faultstring": {
      "lang": "en-US",
      "TEXT": "The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://tempuri.org/:deal. The InnerException message was 'There was an error deserializing the object of type DealioCapLinkLib.Dealio.Models.Deal. The value '2.333333333E9' cannot be parsed as the type 'decimal'.'.  Please see InnerException for more details."
    },
    "detail": {
      "ExceptionDetail": {
        "HelpLink": "",
        "InnerException": {
          "HelpLink": "",
          "InnerException": {
            "HelpLink": "",
            "InnerException": {
              "HelpLink": "",
              "InnerException": "",
              "Message": "Input string was not in a correct format.",
              "StackTrace": "   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)\r\n   at System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt)\r\n   at System.Decimal.Parse(String s, NumberStyles style, IFormatProvider provider)\r\n   at System.Xml.XmlConvert.ToDecimal(String s)\r\n   at System.Xml.XmlConverter.ToDecimal(String value)",
              "Type": "System.FormatException"
            },
            "Message": "The value '2.333333333E9' cannot be parsed as the type 'decimal'.",
            "StackTrace": "   at System.Xml.XmlConverter.ToDecimal(String value)\r\n   at System.Xml.ValueHandle.ToDecimal()\r\n   at System.Xml.XmlBaseReader.ReadContentAsDecimal()\r\n   at System.Xml.XmlDictionaryReader.ReadElementContentAsDecimal()\r\n   at ReadDealFromXml(XmlReaderDelegator , XmlObjectSerializerReadContext , XmlDictionaryString[] , XmlDictionaryString[] )\r\n   at System.Runtime.Serialization.ClassDataContract.ReadXmlValue(XmlReaderDelegator xmlReader, XmlObjectSerializerReadContext context)\r\n   at System.Runtime.Serialization.XmlObjectSerializerReadContext.InternalDeserialize(XmlReaderDelegator reader, String name, String ns, Type declaredType, DataContract& dataContract)\r\n   at System.Runtime.Serialization.XmlObjectSerializerReadContext.InternalDeserialize(XmlReaderDelegator xmlReader, Type declaredType, DataContract dataContract, String name, String ns)\r\n   at System.Runtime.Serialization.DataContractSerializer.InternalReadObject(XmlReaderDelegator xmlReader, Boolean verifyObjectName, DataContractResolver dataContractResolver)\r\n   at System.Runtime.Serialization.XmlObjectSerializer.ReadObjectHandleExceptions(XmlReaderDelegator reader, Boolean verifyObjectName, DataContractResolver dataContractResolver)",
            "Type": "System.Xml.XmlException"
          },
          "Message": "There was an error deserializing the object of type DealioCapLinkLib.Dealio.Models.Deal. The value '2.333333333E9' cannot be parsed as the type 'decimal'.",
          "StackTrace": "   at System.Runtime.Serialization.XmlObjectSerializer.ReadObjectHandleExceptions(XmlReaderDelegator reader, Boolean verifyObjectName, DataContractResolver dataContractResolver)\r\n   at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.PartInfo.ReadObject(XmlDictionaryReader reader, XmlObjectSerializer serializer)\r\n   at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeParameterPart(XmlDictionaryReader reader, PartInfo part, Boolean isRequest)",
          "Type": "System.Runtime.Serialization.SerializationException"
        },
        "Message": "The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://tempuri.org/:deal. The InnerException message was 'There was an error deserializing the object of type DealioCapLinkLib.Dealio.Models.Deal. The value '2.333333333E9' cannot be parsed as the type 'decimal'.'.  Please see InnerException for more details.",
        "StackTrace": "   at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeParameterPart(XmlDictionaryReader reader, PartInfo part, Boolean isRequest)\r\n   at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeParameters(XmlDictionaryReader reader, PartInfo[] parts, Object[] parameters, Boolean isRequest)\r\n   at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeBody(XmlDictionaryReader reader, MessageVersion version, String action, MessageDescription messageDescription, Object[] parameters, Boolean isRequest)\r\n   at System.ServiceModel.Dispatcher.OperationFormatter.DeserializeBodyContents(Message message, Object[] parameters, Boolean isRequest)\r\n   at System.ServiceModel.Dispatcher.OperationFormatter.DeserializeRequest(Message message, Object[] parameters)\r\n   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.DeserializeInputs(MessageRpc& rpc)\r\n   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)\r\n   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)\r\n   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc)\r\n   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)",
        "Type": "System.ServiceModel.Dispatcher.NetDispatcherFaultException"
      }
    }
  }
}

score 5 · Accepted Answer · answered May 22 '19 at 16:10

5

An nvarchar(MAX) can store up to 2GB of characters. Each character in an nvarchar is 2bytes in size. 2GB is 2,000,000,000 bytes so an nvarchar(MAX) can store 2,000,000,000 / 2 characters = 1,000,000,000 characters.

So, to answer your question "Could you fit 20,000 characters into an nvarchar(MAX)?": Yes, you could (50,000 times).

answered May 22 '19 at 16:10

Thom A

88,727
11
45
75

Perhaps they feel that an answer is redundant when the question has been closed with a duplicate. – jmoerdyk May 22 '19 at 16:24
@SMor not sure that a typo is worth a downvote but whatever. Either way you slice it can hold more than 500 of copies of the entire bible or more than 3,000 copies of "War & Peace" which is way more than required for anything. – Sean Lange May 22 '19 at 16:54
Think whomever did downvote also spam flagged the comments, @SeanLange, as my comment, and Smor's are gone. O.o (what typo by the way?) – Thom A May 22 '19 at 17:39
The complaint was 2GB of characters which I don't view as incorrect at all. – Sean Lange May 22 '19 at 18:19

Mike M. · Answer 2 · 2019-05-22T16:12:46.830

1

NVARCHAR(MAX) can handle up to 2GB. If you were using varchar, which is 1 byte per character, that would give you ~2B characters but since you're using nvarchar, 2 bytes per char, that's ~1B characters you could store.

Source (msdn): enter link description here

edited May 22 '19 at 16:12

answered May 22 '19 at 16:10

Mike M.

12,343
1
24
28

How much length can NVARCHAR(MAX) store?

2 Answers2