Maybe there is an answer for this somewhere but, I couldn't find any efficient answer on Google for this question, therefore, I will ask this here.

I wanted to get some information about my Stored Procedure parameters dynamically, so I wrote this script:


The question is what the difference between CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH. Could you give me example of when they would have different results?

enter image description here

Thanks in advance.

  • 2
    Honestly, if you're just dealing with SQL Server, pretend INFORMATION_SCHEMA doesn't exist. They're incomplete, documented as unreliable (see the yellow caution signs here and here), are not being updated with new features or attributes (e.g. try to find info about filtered indexes or include columns), and don't contain anything you can't get from the sys catalog views. – Aaron Bertrand Aug 16 '14 at 12:39
  • ...just don't see any good reason to use them when you have to use the catalog views sometimes anyway. – Aaron Bertrand Aug 16 '14 at 12:55
  • @AaronBertrand So, if I want to get all parameters of stored procedure, how can I do it? – Misha Zaslavsky Aug 16 '14 at 13:36
  • sys.parameters? – Aaron Bertrand Aug 16 '14 at 13:40
  • @AaronBertrand I want to build this string dinamically: DECLARE @p0 INT = , @p1 NVARCHAR(MAX) = sys.parameters is not that comfortable for this because I need the type and the length of the parameter (if it is of type string). There are fields system_type_id/user_type_id for the type, so that I need to make a join to types. I need the max_length which is more problematic because the length of INT is 4 but I don't want to write it, only want to write if it is string, so that I probably need some more code to check if it is the type of string (NVARCHAR, VARCHAR, etc...). – Misha Zaslavsky Aug 16 '14 at 13:52

As described in the MSDN documentation CHARACTER_OCTET_LENGTH is the length in bytes, and CHARACTER_MAXIMUM_LENGTH is the length in characters.

For parameters of type char or varchar they will be the same, but for parameters of type nchar or nvarchar they will be different, with OCTET-LENGTH being twice (usually if not always) the CHARACTER_LENGTH.

  • When it is not twice the CHARACTER_LENGTH for Unicode strings? – Paul Jun 10 '16 at 19:41
  • Check out these two links: kunststube.net/encoding and joelonsoftware.com/articles/Unicode.html – Pieter Geerkens Jun 10 '16 at 19:50
  • I know about encodings. I also assume that if the data is NVARCHAR it means two-bytes Unicode LE (Unicode is not UTF). So my question is "How to put such a data into MSSQL server table with no additional headache?". Is there any UTF field type? – Paul Jun 10 '16 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.