Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 3 of 3

Thread: ntext v. nvarchar

  1. 09-30-2009, 09:01 AM #1
    chazandchaz is offline Member
    Join Date
    Jul 2006
    Posts
    70

    Question ntext v. nvarchar

    I was reading an article on MSSQL recently and it was pointing out that ntext was depreciated in SQL Server 2005 and replaced with nvarchar.

    Is there a reason why the more recent versions of the db create/install script do not create nvarchar or convert ntext to nvarchar?

    I find myself doing a lot of casting on the ntext fields and I didn't know what benefits ntext had over nvarchar, if any.

    Chaz.
  2. 10-23-2009, 09:47 AM #2
    factorite is offline Junior Member
    Join Date
    Oct 2009
    Posts
    22

    Default

    There's no real advantage of text over varchar. It used to be text could store a larger amount, but now with varchar(MAX) that advantage is gone.

    VARCHAR can be properly indexed with Full-text Search and can be used with the LIKE keyword. There are other limitations on the TEXT format as well. If I'm not mistaken, TEXT is not stored as characters ( perhaps as a BLOB?) which is where the limitations come from.

    We've moved all our previous TEXT fields to VARCHAR(MAX).

    NOTE: The only different between NVARCHAR and VARCHAR is NVARCHAR is 16-bit and can handle UNICODE characters.
  3. 11-05-2009, 10:55 AM #3
    SurfAndSwim is offline Junior Member
    Join Date
    Jun 2009
    Posts
    15

    Default

    The previous post is incorrect, ASPDNSF will need to move the ntext fields to navarchar(max) fields. SQL Server 2008 does not support ntext fields. SQL Server 2005 supports both ntext and nvarchar(max), but SQL Server 2000 only supports ntext.

    The reason nvarchar(max) is superior, is that if the amount of data in the field is small, the data is still stored inline with the table. Once the value is exceeded (I forget the default, and this is configured [it might be 256]), then the data is stored separately from the table just like the ntext fields were by default.

    In SQL Server 2005, you can configure the ntext fields to behave like the nvarchar(max) fields by running commands to make the ntext fields store inline up to a certain amount.

    So basically ASPDNSF cannot be run on SQL Server 2008, unless you change every single ntext field to nvarchar(max), and right now I don't know changing the fields would affect the ASPDNSF code or not, so it's best to wait for them to upgrade the system and depricate SQL Server 2000 support.