nvarchar max vs NText

Question

What are the advantages and disadvantages of using the nvarchar max  vs  NText data types in SQL Server  I don t need backward compatibility  so it is fine that nvarchar max  isn t supported in older SQL Server releases   Edit  Apparently the question also applies to TEXT and IMAGE vs  varchar max  and varbinary max   for those searching for those data-types later

User · Answer

ntext will always store its data in a separate database page  while nvarchar max  will try to store the data within the database record itself   So nvarchar max  is somewhat faster  if you have text that is smaller as 8 kB   I also noticed that the database size will grow slightly slower  this is also good   Go nvarchar max

User · Answer

nvarchar max  is what you want to be using  The biggest advantage is that you can use all the T-SQL string functions on this data type  This is not possible with ntext  I m not aware of any real disadvantages

User · Answer

I want to add that you can use the  WRITE clause for partial or full updates and high performance appends to varchar max  nvarchar max  data types   Here you can found full example of using  WRITE clause

User · Answer

VARCHAR MAX  is big enough to accommodate TEXT field  TEXT  NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in future version of SQL Server  SQL Server 2005 provides backward compatibility to data types but it is recommended to use new data types which are VARCHAR MAX   NVARCHAR MAX  and VARBINARY MAX

User · Answer

The advantages are that you can use functions like LEN and LEFT on nvarchar max  and you cannot do that against ntext and text  It is also easier to work with nvarchar max  than text where you had to use WRITETEXT and UPDATETEXT    Also  text  ntext  etc   are being deprecated  http   msdn microsoft com en-us library ms187993 aspx

User · Answer

The biggest disadvantage of Text  together with NText and Image  is that it will be removed in a future version of SQL Server  as by the documentation  That will effectively make your schema harder to upgrade when that version of SQL Server will be released

User · Answer

You should apparently use nvarchar max    MSDN

User · Answer

Wanted to add my experience with converting  I had many text fields in ancient Linq2SQL code  This was to allow text columns present in indexes to be rebuilt ONLINE   First I ve known about the benefits for years  but always assumed that converting would mean some scary long queries where SQL Server would have to rebuild the table and copy everything over  bringing down my websites and raising my heartrate   I was also concerned that the Linq2SQL could cause errors if it was doing some kind of verification of the column type   Happy to report though  that the ALTER commands returned INSTANTLY - so they are definitely only changing table metadata  There may be some offline work happening to bring  lt 8000 character data back to be in-table  but the ALTER command was instant   I ran the following to find all columns needing conversion   SELECT concat  ALTER TABLE dbo     table name     ALTER COLUMN     column name     VARCHAR MAX     table name  column name FROM information schema columns where data type    TEXT  order by table name  column name  SELECT concat  ALTER TABLE dbo     table name     ALTER COLUMN     column name     NVARCHAR MAX     table name  column name FROM information schema columns where data type    NTEXT  order by table name  column name   This gave me a nice list of queries  which I just selected and copied to a new window  Like I said - running this was instant     Linq2SQL is pretty ancient - it uses a designer that you drag tables onto  The situation may be more complex for EF Code first but I haven t tackled that yet

[sql-server] nvarchar(max) vs NText

Examples related to sql-server

Examples related to sql-server-2005

Examples related to sql-server-2008

Examples related to text

Examples related to nvarchar