CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008
Felipe Pessoto
Felipe Pessoto

Reputation: 6969

Are there any concerns moving from ntext to nvarchar(max)

Is there any concern about change my column type from text/ntext to varchar(max)/nvarchar(max)?

Could it break anything? Example, sprocs that has ntext parameters...

Upvotes: 2

Views: 1137

Answers (1)

anon
anon

Reputation:

There are a few concerns, for example if you are currently using the following functions:

You might want to perform a search of your codebase to identify these - grep your applications and/or source control if you use ad hoc SQL, or search stored procedures etc. using the following:

SELECT OBJECT_SCHEMA_NAME([object_id]), OBJECT_NAME([object_id])
  FROM sys.sql_modules
  WHERE [definition] LIKE '%WRITETEXT%'
     OR [definition] LIKE '%READTEXT%'
     OR [definition] LIKE '%UPDATETEXT%'
     OR [definition] LIKE '%TEXTPTR%';

You can also identify procedures and functions with these parameters (I included both TEXT and NTEXT) using:

SELECT OBJECT_SCHEMA_NAME([object_id]), OBJECT_NAME([object_id]), name
  FROM sys.parameters
  WHERE system_type_id IN (35, 99);

And tables/views/TVFs with these column types using:

SELECT OBJECT_SCHEMA_NAME([object_id]), OBJECT_NAME([object_id]), name
  FROM sys.columns
  WHERE system_type_id IN (35, 99);

Or - and I am not sure about all APIs / providers - but some might have an issue swapping out ntext parameters for nvarchar (and you might have to explicitly change some of your code to specify the max length of -1). It's been a long time since I worked on interface code when these types were still in fashion (~1999) so I apologize if my memory is hazy there.

You shouldn't have any breaking changes if your stored procedures continue to take NTEXT parameters, but you won't want to leave those that way for long.

Mostly you should just experience better performance, easier data manipulation, and overall improved compatibility with the new type. Never mind future-proofing!

Upvotes: 6

Related Questions