SQL 2005 table data type "text" to nvarchar

I have this column Remark which is data type text. The longest length is 153.

So is it safe to say that if I change this column from text to nvarchar(200), I won't lose any data?

SELECT LEN(LTRIM(RTRIM( CAST( Remark AS NVARCHAR(MAX) )))) FROM myTable
ORDER BY LEN(LTRIM(RTRIM( CAST( Remark AS NVARCHAR(MAX) )))) DESC

If you want to know what data is there, then:

-
Don't use LTRIM or RTRIM

-
Use DATALENGTH() (for NTEXT add / 2 to the end of that) instead of LEN

Now, converting from either TEXT or NTEXT to NVARCHAR won't result in data loss if the length is the same or larger than what was calculated assuming what is mentioned above.

HOWEVER, the real issue isn't what data is there, it's what does the app allow for. If the app allows for more than 200 characters, then 200 isn't a safe choice. Make sure that all sources that can insert or update data in this column enforce a limit of 200 characters. If even one does not, then you need to work with the devs to make sure any sources of input are updated to reflect a limit of 200, or whatever you agree on (just because nobody has yet used even 200 characters doesn't necessarily imply that there isn't a valid case for going above that limit, if the app allows for it).

I just had the thought that there could be a slight issue with maximum row size since the TEXT, NTEXT, and IMAGE datatypes, by default, are a 16 byte pointer in the row with the actual data off row. Since you only get approximately 8060 bytes per row, if you were less than 400 bytes from that limit, then changing TEXT (requiring 16 bytes) to NVARCHAR(200) (requiring 400 bytes) could potentially result in an error. However, I just tried it and doesn't error; it allows for going above the 8060 byte limit (keep in mind that the max row size is still approx. 8060, but any extra data will be placed on ROW_OVERFLOW pages).

USE [tempdb];

-- DROP TABLE dbo.AlterColumnTest;
CREATE TABLE dbo.AlterColumnTest
(
  [ID] INT NOT NULL PRIMARY KEY,
  [EnglishSource] VARCHAR(50) COLLATE Latin1_General_CI_AS,
  [EnglishText] TEXT COLLATE Latin1_General_CI_AS,
  [HebrewSource] VARCHAR(50) COLLATE Hebrew_CI_AS,
  [HebrewText] TEXT COLLATE Hebrew_CI_AS,
  [Spacer] CHAR(7500) NOT NULL DEFAULT ('')
);

DECLARE @TestString VARBINARY(100);
SET @TestString = 0x4120746573743A20C0C620CBE0E2E8EC20F0F4F92021;
SELECT CONVERT(VARCHAR(50), @TestString); -- A test: ÀÆ Ëàâèì ðôù !

INSERT INTO dbo.AlterColumnTest ([ID], [EnglishSource], [HebrewSource])
VALUES (1, @TestString, @TestString);

SELECT * FROM dbo.AlterColumnTest;

UPDATE dbo.AlterColumnTest
SET    [EnglishText] = [EnglishSource],
       [HebrewText] = [HebrewSource];

SELECT * FROM dbo.AlterColumnTest;

ALTER TABLE dbo.AlterColumnTest
  ALTER COLUMN [EnglishText] NVARCHAR(200) COLLATE Latin1_General_CI_AS;

ALTER TABLE dbo.AlterColumnTest
  ALTER COLUMN [HebrewText] NVARCHAR(200) COLLATE Hebrew_CI_AS;

SELECT * FROM dbo.AlterColumnTest;

SELECT *
FROM   sys.columns sc
WHERE  sc.[object_id] = OBJECT_ID(N'dbo.AlterColumnTest');

Values for @TestString taken from:

• Code Page 1252 Windows Latin 1 (ANSI)

• Code Page 1255 Windows Hebrew

StackExchange Database Administrators Q#158345, answer score: 2