将nvarchar (4000)改为nvarchar(500)

Question

我对nvarchar(4000)有一个性能问题。我想按照下面的声明修改该表：

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

它会影响数据或索引吗？我们在表中有超过一千万行。

Answer 1

我首先要提醒大家，将来最大长度为500的可能性太小了。

这就是说，您的代码将工作，尽管它将在表上使用模式修改锁，如果任何索引引用该列，则无法运行。

这里有一篇很好的文章，介绍了模式修改锁：Sch-M锁是邪恶的可能会出现的一些问题。

理想情况下，您在维护窗口中执行此操作，此时没有事务正在访问表，并且您有一些时间进行处理。

下面是一些示例代码，您可以使用这些代码了解所涉及的时间：

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

Answer 2

是的，缩短列长度将涉及重新处理表中的所有行以及可能的索引。

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