If you’re writing an SQL upgrade script that updates a field in your table from NVARCHAR(n) data type to NVARCHAR(max) and the script is designed to run many times – an easy way to avoid multiple table alteration is to check for max character length for that field, i.e.:

IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MY_TABLE' AND COLUMN_NAME = 'MY_COLUMN')<>-1
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ALTER COLUMN [MY_COLUMN] [nvarchar](max) NOT NULL
END

This code checks length of the field which is specific number for standard NVARCHAR and -1 for NVARCHAR(max), and alters column type only if it hasn’t already been altered.