5

I have read about Are there any disadvantages to always using nvarchar(MAX) and varchar(max) vs varchar(255) from different forums. Now, I wonder if it is ideal/safe practice to convert a text datatype to nvarchar(MAX)? Are there any performance difference? or do I need to specify the size (as innvarchar(255)) instead of nvarchar(max)?

Community
  • 1
  • 1
Mark
  • 8,046
  • 15
  • 48
  • 78
  • Related: http://stackoverflow.com/questions/8512164/is-varcharmax-always-preferable – Jane S Jul 20 '15 at 01:19
  • 3
    If you know the size is less than 4000 (or 8000 for `varchar()`), then you should use the smaller value. But, if `text` was used in an old version of SQL Server, then the right replacement is `max`. – Gordon Linoff Jul 20 '15 at 01:25
  • 1
    I don't actually know the actual length since it is migrated from another database. BTW, it came from SQL Server 2000. I am having problems in migrating triggers when I try to create using the `text` datatype. Just to be safe, I want to know if it has a negative impact if I use `nvarchar(MAX)`. – Mark Jul 20 '15 at 02:09

1 Answers1

5

Answer is very simple: text type is deprecated. So, yes, you should convert a text datatype to varchar(MAX).

https://msdn.microsoft.com/en-AU/library/ms187993.aspx

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

On top of that, if you know that the size of your text is less than 8000, then use varchar(nnn) instead of varchar(max). varchar(nnn) is more efficient than varchar(max).

Note: text type is for non-unicode text, so use varchar instead. ntext is for unicode text, so use nvarchar instead.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
  • I think the point of the original poster's question is more around what are the downsides (if any) of using NVARCHAR(max) instead of TEXT. I can think of one, and that is the act of converting itself. If you're doing it on a big, busy table that has to be up 24/7, your average DBA or developer might baulk at the prospect and leave well enough alone. The cost of developing and thoroughly testing a conversion might frighten people too. In a risk-averse organisation, the safety of doing nothing might outweigh the benefits of any change. – DatumPoint Jul 30 '20 at 01:03