none
NVarchar(max)

    Question

  • Relatively simple question but I'm looking to store a medium to large amounts of textual data from questionnaires I'm creating.  I'm wondering if using nvarchar with Max would be the best choice.  I know the difference between varchar and nvarchar and nvarchar will definitely be needed in my case.  But I don't know what limitations I want to set for each of my Q&A and would rather leave it open ended as opposed to blocking myself in with some random limitation.  But if the max somehow swells my tables and thus database size, maybe it isn't a good idea either.  What do you think?
    Wednesday, October 20, 2010 9:10 PM

Answers

  • Hi ilselah,

    By default, nvarchar(MAX) values are stored exactly the same as nvarchar(4000) values would be, unless the actual length exceed 4000 characters; in that case, the in-row data is replaced by a pointer to one or more seperate pages where the data is stored.

    If you anticipate data possibly exceeding 4000 character, nvarchar(MAX) is definitely the recommended choice.


    -- Hugo Kornelis, SQL Server MVP
    • Marked as answer by illselah Wednesday, October 20, 2010 10:47 PM
    Wednesday, October 20, 2010 9:20 PM
  • Hi,

    From the storage prospective there are no difference between nvarchar(max) and nvarchar(N) when N < 4000. Data is stored in row or on the Row-overflow pages when does not fit. When you use nvarchar(max) and stores more than 4000 characters (8000 bytes) SQL Server uses different method to store the data - similar to old TEXT data type - it stores in the LOB pages.

    Performance-wise - again, for N<4000 and (max) - there is no difference. Well, technically it affects row size estimation and could introduce some issues - you can read more about it here: http://aboutsqlserver.com/2010/08/18/what-is-the-optimal-size-for-variable-width-columns/ .

    What can affect the performance of the system is the row size. If you have queries that SCAN table, large row size will lead to more data pages per table -> more io operations -> performance degradation. If this is the case, you can try to do the vertical partitioning and move nvarchar field to the different table. Take a look at http://aboutsqlserver.com/2010/09/15/vertical-partitioning-as-the-way-to-reduce-io/ for more details. This makes sense only if:

    a. You have a lot of data

    b. You have large # of SCANS that do not require that field.

    Research this approach - it could benefit the system but same time it could make the situation worse.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Naomi NEditor Wednesday, October 20, 2010 9:43 PM
    • Marked as answer by illselah Wednesday, October 20, 2010 10:47 PM
    Wednesday, October 20, 2010 9:27 PM

All replies

  • Hi ilselah,

    By default, nvarchar(MAX) values are stored exactly the same as nvarchar(4000) values would be, unless the actual length exceed 4000 characters; in that case, the in-row data is replaced by a pointer to one or more seperate pages where the data is stored.

    If you anticipate data possibly exceeding 4000 character, nvarchar(MAX) is definitely the recommended choice.


    -- Hugo Kornelis, SQL Server MVP
    • Marked as answer by illselah Wednesday, October 20, 2010 10:47 PM
    Wednesday, October 20, 2010 9:20 PM
  • Hi,

    From the storage prospective there are no difference between nvarchar(max) and nvarchar(N) when N < 4000. Data is stored in row or on the Row-overflow pages when does not fit. When you use nvarchar(max) and stores more than 4000 characters (8000 bytes) SQL Server uses different method to store the data - similar to old TEXT data type - it stores in the LOB pages.

    Performance-wise - again, for N<4000 and (max) - there is no difference. Well, technically it affects row size estimation and could introduce some issues - you can read more about it here: http://aboutsqlserver.com/2010/08/18/what-is-the-optimal-size-for-variable-width-columns/ .

    What can affect the performance of the system is the row size. If you have queries that SCAN table, large row size will lead to more data pages per table -> more io operations -> performance degradation. If this is the case, you can try to do the vertical partitioning and move nvarchar field to the different table. Take a look at http://aboutsqlserver.com/2010/09/15/vertical-partitioning-as-the-way-to-reduce-io/ for more details. This makes sense only if:

    a. You have a lot of data

    b. You have large # of SCANS that do not require that field.

    Research this approach - it could benefit the system but same time it could make the situation worse.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Naomi NEditor Wednesday, October 20, 2010 9:43 PM
    • Marked as answer by illselah Wednesday, October 20, 2010 10:47 PM
    Wednesday, October 20, 2010 9:27 PM
  • Thanks guys.  You both provided great answers.  I think I'm going with the max then.
    Wednesday, October 20, 2010 10:47 PM