不支援在記憶體優化數據表上執行 ALTER 作業。 這包括變更bucket_count、新增或移除索引，以及新增或移除數據行等作業。 本主題提供如何更新記憶體優化數據表的指導方針。
更新 Memory-Optimized 數據表的定義
更新記憶體優化數據表的定義需要您建立具有更新數據表定義的新數據表、將數據複製到新的數據表，以及開始使用新的資料表。 除非數據表是只讀的，否則這需要停止數據表上的工作負載，以確保在執行數據複製時不會對數據表進行任何變更。
下列程式概述更新數據表所需的步驟。 在此範例中，更新會新增索引。 此程式會保留數據表的名稱，而且需要兩個數據複製作業：一次到臨時表，一次到新的數據表。 變更索引的 bucket_count 或增加或移除欄位的方式相同。
停止數據表上的工作負載。
產生數據表的腳本，並將新的索引新增至腳本。
為架構系結物件產生腳本，其中主要包含原生編譯的儲存程序，引用至 T 及其許可權。
您可以使用下列查詢找到參考資料表的架構系結物件：
declare @t nvarchar(255) = N'<table name>' select r.referencing_schema_name, r.referencing_entity_name from sys.dm_sql_referencing_entities (@t, 'OBJECT') as r join sys.sql_modules m on r.referencing_id=m.object_id where r.is_caller_dependent = 0 and m.is_schema_bound=1;預存程式的權限可以使用下列 Transact-SQL 編寫腳本：
declare @sp nvarchar(255) = N'<procedure name>' declare @permissions nvarchar(max) = N'' select @permissions += dp.state_desc + N' ' + dp.permission_name + N' ON ' + quotename(schema_name(o.schema_id)) + N'.' + quotename(o.name) + N' TO ' + quotename(u.name) + N'; ' + char(13) from sys.database_permissions as dp join sys.database_principals as u on u.principal_id = dp.grantee_principal_id join sys.objects as o on o.object_id = dp.major_id where dp.class = 1 /* object */ and dp.minor_id = 0 and o.object_id=object_id(@sp); select @permissions建立資料表的複本，並將數據從原始數據表複製到數據表的複本。 您可以使用下列 Transact-SQL1 來建立複本。
select * into dbo.T_copy from dbo.T如果有足夠的可用記憶體，
T_copy可以是記憶體優化的資料表，這樣可以使資料複製速度更快。卸除參考原始數據表的架構系結物件。
卸除原始數據表。
使用包含新索引的腳本建立新的數據表 （
T）。複製資料從
T_copyT複製到 。重新建立參考架構系結物件並套用許可權。
在
T上啟動工作負載。
1 請注意，此 T_copy 範例中會保存至磁碟。 如果的備份 T 可供使用， T_copy 可能是暫時或非持久性數據表。
2 必須有足夠的記憶體。T_copy 在DROP TABLE上，記憶體不會立即釋放。 如果 T_copy 已進行記憶體優化，則必須有足夠的記憶體來容納額外的兩個 T 副本。 如果 T_copy 是磁碟型資料表，則只需要有足夠的記憶體供一個額外的副本 T 使用，因為垃圾回收程序在刪除舊版本 T 之後需要進行補償處理。
變更架構 （PowerShell）
下列 PowerShell 腳本通過編寫數據表和相關聯權限的腳本，來準備和執行架構變更。
prepare_schema_change.ps1 <serverName> <databaseName> <schemaName> <tableName>
此腳本將資料表作為參數，生成物件及其權限的腳本，並引用目前資料夾中與架構綁定之物件及其權限。 總共會產生 7 個腳本來更新輸入資料表的架構：
將資料複製到臨時表（heap）。
卸除參考數據表的架構系結物件。
刪除數據表。
使用新的架構重新建立數據表，並重新套用許可權。
將數據從臨時表複製到重新建立的數據表。
重新建立參考數據表及其許可權的架構系結物件。
刪除臨時表。
應更新步驟 4 的腳本，以反映所需的架構變更。 如果數據表的數據行有任何變更，步驟 5 的腳本（從臨時表複製數據）和 6（重新建立預存程式）應視需要更新。
# Prepare for schema changes by scripting out the table, as well as associated permissions
# Usage: prepare_schema_change.ps1 server_name db_name schema_name table_name
# stop execution once an error occurs
$ErrorActionPreference="Stop"
if($args.Count -le 3)
{
throw "Usage prepare_schema_change.ps1 server_name db_name schema_name table_name"
}
$servername = $args[0]
$database = $args[1]
$schema = $args[2]
$object = $args[3]
$object_heap = "$object$(Get-Random)"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$server = New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($servername)
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)
## initialize table variable
$tableUrn = $server.Databases[$database].Tables[$object, $schema]
if($tableUrn.Count -eq 0)
{
throw "Table or database not found"
}
## initialize scripting object
$scriptingOptions = New-Object ("Microsoft.SqlServer.Management.SMO.ScriptingOptions")
$scriptingOptions.Permissions = $True
$scriptingOptions.ScriptDrops = $True
$scripter.Options = $scriptingOptions;
Write-Host "(1) Scripting SELECT INTO $object_heap for table [$object] to 1_copy_to_heap_for_$schema`_$object.sql"
Echo "SELECT * INTO $schema.$object_heap FROM $schema.$object WITH (SNAPSHOT)" | Out-File "1_copy_to_heap_$schema`_$object.sql";
Write-Host "--done--"
Write-Host ""
Write-Host "(2) Scripting DROP for procs schema-bound to [$object] 2_drop_procs_$schema`_$object.sql"
## query referencing schema-bound objects
$dt = $server.Databases[$database].ExecuteWithResults("select r.referencing_schema_name, r.referencing_entity_name
from sys.dm_sql_referencing_entities ('$schema.$object', 'OBJECT') as r join sys.sql_modules m on r.referencing_id=m.object_id
where r.is_caller_dependent = 0 and m.is_schema_bound=1;")
## initialize out file
Echo "" | Out-File "2_drop_procs_$schema`_$object.sql"
## loop through schema-bound objects
ForEach ($t In $dt.Tables)
{
ForEach ($r In $t.Rows)
{
## script object
$so = $server.Databases[$database].StoredProcedures[$r[1], $r[0]]
$scripter.Script($so) | Out-File -Append "2_drop_procs_$schema`_$object.sql"
}
}
Write-Host "--done--"
Write-Host ""
Write-Host "(3) Scripting DROP table for [$object] to 3_drop_table_$schema`_$object.sql"
$scripter.Script($tableUrn) | Out-File "3_drop_table_$schema`_$object.sql";
Write-Host "--done--"
Write-Host ""
## now script creates
$scriptingOptions.ScriptDrops = $False
Write-Host "(4) Scripting CREATE table and permissions for [$object] to !please_edit_4_create_table_$schema`_$object.sql"
Write-Host "***** rename this script to 4_create_table.sql after completing the updates to the schema"
$scripter.Script($tableUrn) | Out-File "!please_edit_4_create_table_$schema`_$object.sql";
Write-Host "--done--"
Write-Host ""
Write-Host "(5) Scripting INSERT INTO table from heap and UPDATE STATISTICS for [$object] to 5_copy_from_heap_$schema`_$object.sql"
Write-Host "[update this script if columns are added to or removed from the table]"
Echo "INSERT INTO [$schema].[$object] SELECT * FROM [$schema].[$object_heap]; UPDATE STATISTICS [$schema].[$object] WITH FULLSCAN, NORECOMPUTE" | Out-File "5_copy_from_heap_$schema`_$object.sql";
Write-Host "--done--"
Write-Host ""
Write-Host "(6) Scripting CREATE PROC and permissions for procedures schema-bound to [$object] to 6_create_procs_$schema`_$object.sql"
Write-Host "[update the procedure definitions if columns are renamed or removed]"
## initialize out file
Echo "" | Out-File "6_create_procs_$schema`_$object.sql"
## loop through schema-bound objects
ForEach ($t In $dt.Tables)
{
ForEach ($r In $t.Rows)
{
## script the schema-bound object
$so = $server.Databases[$database].StoredProcedures[$r[1], $r[0]]
ForEach($s In $scripter.Script($so))
{
Echo $s | Out-File -Append "6_create_procs_$schema`_$object.sql"
Echo "GO" | Out-File -Append "6_create_procs_$schema`_$object.sql"
}
}
}
Write-Host "--done--"
Write-Host ""
Write-Host "(7) Scripting DROP $object_heap to 7_drop_heap_$schema`_$object.sql"
Echo "DROP TABLE $schema.$object_heap" | Out-File "7_drop_heap_$schema`_$object.sql";
Write-Host "--done--"
Write-Host ""
下列 PowerShell 腳本會執行先前範例中所編寫腳本的架構變更。 此腳本會採用 數據表作為自變數，並執行針對該數據表和相關聯預存程式所產生的架構變更腳本。
使用量：execute_schema_change.ps1 server_name**db_nameschema_nametable_name
# stop execution once an error occurs
$ErrorActionPreference="Stop"
if($args.Count -le 3)
{
throw "Usage execute_schema_change.ps1 server_name db_name schema_name table_name"
}
$servername = $args[0]
$database = $args[1]
$schema = $args[2]
$object = $args[3]
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$server = New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($servername)
$database = $server.Databases[$database]
$table = $database.Tables[$object, $schema]
if($table.Count -eq 0)
{
throw "Table or database not found"
}
$1 = Get-Item "1_copy_to_heap_$schema`_$object.sql"
$2 = Get-Item "2_drop_procs_$schema`_$object.sql"
$3 = Get-Item "3_drop_table_$schema`_$object.sql"
$4 = Get-Item "4_create_table_$schema`_$object.sql"
$5 = Get-Item "5_copy_from_heap_$schema`_$object.sql"
$6 = Get-Item "6_create_procs_$schema`_$object.sql"
$7 = Get-Item "7_drop_heap_$schema`_$object.sql"
Write-Host "(1) Running SELECT INTO heap for table [$object] from 1_copy_to_heap_for_$schema`_$object.sql"
$database.ExecuteNonQuery("$(Echo $1.OpenText().ReadToEnd())")
Write-Host "--done--"
Write-Host ""
Write-Host "(2) Running DROP for procs schema-bound from [$object] 2_drop_procs_$schema`_$object.sql"
$database.ExecuteNonQuery("$(Echo $2.OpenText().ReadToEnd())")
Write-Host "--done--"
Write-Host ""
Write-Host "(3) Running DROP table for [$object] to 4_drop_table_$schema`_$object.sql"
$database.ExecuteNonQuery("$(Echo $3.OpenText().ReadToEnd())")
Write-Host "--done--"
Write-Host ""
Write-Host "(4) Running CREATE table and permissions for [$object] from 4_create_table_$schema`_$object.sql"
$database.ExecuteNonQuery("$(Echo $4.OpenText().ReadToEnd())")
Write-Host "--done--"
Write-Host ""
Write-Host "(5) Running INSERT INTO table from heap for [$object] and UPDATE STATISTICS from 5_copy_from_heap_$schema`_$object.sql"
$database.ExecuteNonQuery("$(Echo $5.OpenText().ReadToEnd())")
Write-Host "--done--"
Write-Host ""
Write-Host "(6) Running CREATE PROC and permissions for procedures schema-bound to [$object] from 6_create_procs_$schema`_$object.sql"
$database.ExecuteNonQuery("$(Echo $6.OpenText().ReadToEnd())")
Write-Host "--done--"
Write-Host ""
Write-Host "(7) Running DROP heap from 7_drop_heap_$schema`_$object.sql"
$database.ExecuteNonQuery("$(Echo $7.OpenText().ReadToEnd())")
Write-Host "--done--"
Write-Host ""