Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeUsing DNN Platf...Using DNN Platf...Performance and...Performance and...NVARCHAR(MAX) vs NTEXT DatatypeNVARCHAR(MAX) vs NTEXT Datatype
Previous
 
Next
New Post
3/14/2015 7:52 AM
 

While trying to clean up some of the html code in a third party news articles module using REPLACE SQL statement, have discovered that the text column is in NTEXT datatype, hence it did not accept the REPLACE statement.

What really surprised me that many of the core DNN tables have columns in NTEXT datatype.

Without going into the merits of NVARCHAR datatype vs. NTEXT, the latter is likely to be deprecated in the near future as announced by Microsoft in favor of NVARCHAR(MAX)  (please see bottom below).

Going back to the issue of using the REPLACE statement to clean up the text colum, I had to run a script to change the datatype of the entire database from NTEXT to NVARCHAR(MAX) using a script I found on:

How to quickly convert all NTEXT columns to NVARCHAR(MAX) in a SQL Server database

My question why would DNN core development team still cling  to an datatype that will be removed in the near future as per Microsoft announcement, in addition to the limitations  of NTEXT datatype?

Second part of my question, would running the "convert all NTEXT columns to NVARCHAR(MAX) script", affect any future upgrades.  I executivbed the script on a public clone of my existing installation with no apparent changes or issues other than a noticeable increase in performance of the clone installation.

The script was successfully executed on a core installation of the DNN CE 7.3.4 with the below results:

ALTER TABLE [dbo].[aspnet_Membership] ALTER COLUMN [Comment] NVARCHAR(MAX)  NULL;

UPDATE [dbo].[aspnet_Membership] SET [Comment] = [Comment];

ALTER TABLE [dbo].[ScheduleHistory] ALTER COLUMN [LogNotes] NVARCHAR(MAX)  NULL;

UPDATE [dbo].[ScheduleHistory] SET [LogNotes] = [LogNotes];

ALTER TABLE [dbo].[Packages] ALTER COLUMN [License] NVARCHAR(MAX)  NULL;

UPDATE [dbo].[Packages] SET [License] = [License];

ALTER TABLE [dbo].[Packages] ALTER COLUMN [Manifest] NVARCHAR(MAX)  NULL;

UPDATE [dbo].[Packages] SET [Manifest] = [Manifest];

ALTER TABLE [dbo].[Packages] ALTER COLUMN [ReleaseNotes] NVARCHAR(MAX)  NULL;

UPDATE [dbo].[Packages] SET [ReleaseNotes] = [ReleaseNotes];

ALTER TABLE [dbo].[EventLog] ALTER COLUMN [LogProperties] NVARCHAR(MAX) NOT NULL;

UPDATE [dbo].[EventLog] SET [LogProperties] = [LogProperties];

ALTER TABLE [dbo].[Profile] ALTER COLUMN [ProfileData] NVARCHAR(MAX) NOT NULL;

UPDATE [dbo].[Profile] SET [ProfileData] = [ProfileData];

ALTER TABLE [dbo].[SystemMessages] ALTER COLUMN [MessageValue] NVARCHAR(MAX) NOT NULL;

UPDATE [dbo].[SystemMessages] SET [MessageValue] = [MessageValue];

ALTER TABLE [dbo].[HtmlText] ALTER COLUMN [Content] NVARCHAR(MAX)  NULL;

UPDATE [dbo].[HtmlText] SET [Content] = [Content];

ALTER TABLE [dbo].[HtmlText] ALTER COLUMN [Summary] NVARCHAR(MAX)  NULL;

UPDATE [dbo].[HtmlText] SET [Summary] = [Summary];

ALTER TABLE [dbo].[ProfilePropertyDefinition] ALTER COLUMN [DefaultValue] NVARCHAR(MAX)  NULL;

UPDATE [dbo].[ProfilePropertyDefinition] SET [DefaultValue] = [DefaultValue];

ALTER TABLE [dbo].[Vendors] ALTER COLUMN [KeyWords] NVARCHAR(MAX)  NULL;

UPDATE [dbo].[Vendors] SET [KeyWords] = [KeyWords];

ALTER TABLE [dbo].[EventQueue] ALTER COLUMN [Attributes] NVARCHAR(MAX) NOT NULL;

UPDATE [dbo].[EventQueue] SET [Attributes] = [Attributes];

(2 row(s) affected)

(1095 row(s) affected)

(119 row(s) affected)

(119 row(s) affected)

(119 row(s) affected)

(74 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

(3 row(s) affected)

(3 row(s) affected)

(45 row(s) affected)

(0 row(s) affected)

(15 row(s) affected)

EXECUTE sp_refreshview '[dbo].[vw_Lists]';

EXECUTE sp_refreshview '[dbo].[vw_MessagesForDispatch]';

EXECUTE sp_refreshview '[dbo].[vw_CoreMessaging_Messages]';

EXECUTE sp_refreshview '[dbo].[vw_aspnet_Applications]';

EXECUTE sp_refreshview '[dbo].[vw_Users]';

EXECUTE sp_refreshview '[dbo].[vw_aspnet_Users]';

EXECUTE sp_refreshview '[dbo].[vw_Portals]';

EXECUTE sp_refreshview '[dbo].[vw_PortalsDefaultLanguage]';

EXECUTE sp_refreshview '[dbo].[vw_Files]';

EXECUTE sp_refreshview '[dbo].[vw_PublishedFiles]';

EXECUTE sp_refreshview '[dbo].[vw_aspnet_MembershipUsers]';

EXECUTE sp_refreshview '[dbo].[vw_TabModules]';

EXECUTE sp_refreshview '[dbo].[vw_Modules]';

EXECUTE sp_refreshview '[dbo].[vw_Tabs]';

EXECUTE sp_refreshview '[dbo].[vw_ContentWorkflowStatePermissions]';

EXECUTE sp_refreshview '[dbo].[vw_Profile]';

EXECUTE sp_refreshview '[dbo].[vw_UserRoles]';

EXECUTE sp_refreshview '[dbo].[vw_DesktopModules]';

EXECUTE sp_refreshview '[dbo].[vw_DesktopModulePermissions]';

EXECUTE sp_refreshview '[dbo].[vw_FolderPermissions]';

EXECUTE sp_refreshview '[dbo].[vw_ModulePermissions]';

EXECUTE sp_refreshview '[dbo].[vw_TabPermissions]';

EXECUTE sp_refreshview '[dbo].[vw_ExtensionUrlProviders]';

 

Microsoft has stated:

Important

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.

reference link:
ntext, text, and image (Transact-SQL)

 
New Post
3/14/2015 10:11 AM
 
Salem,
it is a known issue, I have on my list for one of the next versions of TurboScripts to review data type of all columns and replace nText with nVarchar, adjust Text sizes (from memory, e.g. dnn uses 4 to 5 different field sizes for CultureCode) and a couple of other types.
Be aware it is NOT sufficient to just modify columns in table and refresh views, there are functions and especially stored procedures to be adjusted as well.
And there are strange constructs like UserProfile, where PropertyValue is an nVarchar(3750), preventing from being indexed, while PropertyText is nVarChar(Max), which should be used for any text with more than 127 or 256 characters.

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
3/14/2015 11:15 AM
 

Many thanks Sebastian for your elaborate reply, well understood!  I will thus refrain from running the referred scripts on my production installation and will wait for your updated script on codeplex.

 

 

 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Performance and...Performance and...NVARCHAR(MAX) vs NTEXT DatatypeNVARCHAR(MAX) vs NTEXT Datatype


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.

Content Layout

Subscribe to DNN Digest

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out