0

I have a performance issue with nvarchar(4000). I want to alter the table as per the statement below:

BEGIN  declare @length as int  
SELECT @length = MAX(LEN([SourceId])) 
from [dbo].[table] 
if   @length < 500  
begin  ALTER TABLE  [dbo].[table]   ALTER COLUMN [SourceId] NVARCHAR(500) NOT NULL;  end   
else   select ' column Length greater than 500' as tb  end

Will it impact data or indexes? We have over 10 million rows in the table.

2 Answers 2

1

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: The Sch-M lock is Evil by Michael J Swart.

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.
*/
-2

Yes shortening column lengths will involve reprocessing all the rows in the table and possibly indexes

https://michaeljswart.com/2013/04/altering-text-columns-only-a-metadata-change/

1
  • Please don’t create what is effectively a link-only answer. Jun 10, 2021 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.