sql server – changing nvarchar (4000) to nvarchar(500)

I would first caution you about the possibility of a max length of 500 being too small in the future.

That said, your code will work, though it will take a schema modification lock on the table and cannot be run if any indexes reference that column. Here’s a excellent article about some of the issues that could occur with a schema modification lock: https://michaeljswart.com/2013/04/the-sch-m-lock-is-evil/#:~:text=Sch%2DM%20is%20an%20abbreviation,M%20lock%20on%20that%20table. Ideally you are doing this in a maintenance window when no transactions are hitting the table and you have some time for it to process.

Here is some example code that you can use to get an idea of the time involved:


/* Create Table */
DROP TABLE IF EXISTS dbo._Test 
CREATE TABLE dbo._Test 
(
ID INT IDENTITY (1,1) NOT NULL CONSTRAINT PK__TEST PRIMARY KEY CLUSTERED,
SourceID NVARCHAR(4000) NOT NULL
)

/* Optional Index */
CREATE NONCLUSTERED INDEX SourceID ON dbo._Test (SourceID) 

/* Load Table */ 
INSERT INTO dbo._Test (SourceID)

SELECT 
TOP 10000000
O1.Name
FROM 
sys.objects O1
CROSS JOIN sys.objects O2


/* Attempt ALTER */

ALTER TABLE dbo._Test 
ALTER COLUMN SourceID NVARCHAR(500)

/* 
    Fails Due To Index Depending On Column

Msg 5074, Level 16, State 1, Line 26
The index 'SourceID' is dependent on column 'SourceID'.
Msg 4922, Level 16, State 9, Line 26
ALTER TABLE ALTER COLUMN SourceID failed because one or more objects access this column.

*/

/* Drop the index */
DROP INDEX IF EXISTS SourceID ON dbo._Test

/* Try Again */

ALTER TABLE dbo._Test 
ALTER COLUMN SourceID NVARCHAR(500)

/*
Notice this takes a schema modification lock

    <Object name="_Test" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="20" />
      </Locks>

Total time was ≈ 25 seconds on my test system, then may need to add back any indexes or constraints that were referencing the altered column.
*/