Want to convert my nvarchar(512) to nvarchar(max)

Question

Want to convert my nvarchar(512) to nvarchar(max)

rajesh-210109

Old Hand

Points: 339
More actions
February 24, 2010 at 10:34 am

#139042

Hello ,
I have a column which has nvarchar(512) of DataType. Because of new requirement my column size could very to any size. I want to change my current Datatype nvarchar(512) to nvarchar(max).
I am not sure what will be the effect on size usage by navarchar(max). How nvarchar(n) stores the data and nvarchar(max) stores the data?
Appreciate your help..
Thanks,
Rajesh Patel

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ray K SSC-Dedicated Points: 31148 More actions · Answer 1

I don't think there's an adverse effect. (Someone correct me if I'm wrong.)

Where you'd have a problem is when you go the other way -- that is, from nvarchar(MAX) to nvarchar(512). If you have data longer than 512 characters, you'll have truncation issues.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

rajesh-210109 Old Hand Points: 339 More actions · Answer 2

Thanks for your response.

Can you give me some details about the memory usage of both Datatype(nvarchar(n) and nvarchar(max)?

Tonja Blackwell Old Hand Points: 347 More actions · Answer 3

nvarchar(x) or nvarchar(max) the only difference to me is the difference between x value and the max value (which i think is 4000...someone please correct if I am mistaken). NVARCHAR is used to store unicode characters and is primarily used for multlilanguage. One character uses 16 bits so it is almost twice as large as one varchar character.

I hope that helps.

Simple Musings From a Simple Developer

TonjaB

rajesh-210109 Old Hand Points: 339 More actions · Answer 4

If i change my column type from nvarchar(512) to nvarchar(max), will it degrade the performance?

Tonja Blackwell Old Hand Points: 347 More actions · Answer 5

It should not degrade performance to any noticeable degree. However, not knowing your data or the volume of data I say that with no guarantee. The only thing I would be concerned about is if you want to save any data back to the orginal NVARCHAR(512) column then there might be truncation issues if the data in the source column is longer than the 512.

TonjaB

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

There is a significant difference between varchar(4000) and varchar(max). When specifying the size the number must be between 1 and 4,000. Max however behaves differently. The max amount of data when using max is 2^31-1 bytes.

Check out BOL: http://msdn.microsoft.com/en-us/library/ms186939.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Tonja Blackwell Old Hand Points: 347 More actions · Answer 7

Thanks for the clarification Sean. I incorrectly assumed by max the max 4000 was implied.

TonjaB

Paul White SSC Guru Points: 150467 More actions · Answer 8

rajesh-210109 (2/24/2010)
If i change my column type from nvarchar(512) to nvarchar(max), will it degrade the performance?

It might do. While the physical storage won't change much (the Storage Engine pretty much takes MAX to mean: use 'normal' NVARCHAR storage if you can, otherwise use a true LOB structure), the processing side of things might. When SQL Server plans a query execution, it must take account of the maximum possible size of the data type. Just because you know that no data exceeds 1024 bytes (at present) doesn't mean it is safe for the Query Optimizer to assume that. Coping with potential data sizes of 2GB can often turn a query plan for the worse. Test carefully, and keep an eye on it.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi