Search code examples
sqlsql-server-cenvarchar

How to insert data type nvarchar more than 4000 characters in SQL Server CE?

I insert string with more than 4000 characters into a nvarchar column, so SQL Server CE is throwing an error.

I know ntext can store more than 4000 characters, but it almost support in future.

How way can I insert string with more than 4000 characters to column nvarchar so SQL Server CE ?

Solution

  • Unfortunately the data type options are limited in SQL CE. You will need to use ntext to support more than 4000 characters.

    note: ntext is no longer supported in string functions.

    You will not be able to compare or sort ntext except when using is null or like.

    The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates

    You will be able to select, update, delete, insert with ntext as long as you are not trying to compare the value of the ntext column with the exception of is null or like.

    So you can not:

    update t 
set ntxt = 'I miss nvarchar(max)' 
where ntxt = 'I am using sql ce'

    But you can 

    update t 
set ntxt = 'I miss nvarchar(max)' 
where ntxt like '%sql ce'