Sql-Server
在 Alter Views 上包含索引腳本
在開發人員更改索引視圖以添加評論後，在其上創建的聚集索引被刪除，沒有任何警告。
這導致了失敗，因為有幾個程序有
WITH (NOEXPAND)提示。我目前設置了 SSMS，以便右鍵點擊
VIEW並選擇SCRIPT VIEW as，CREATE TO包括索引定義。有沒有辦法在
VIEWSfor上編寫索引ALTER to？
好的，讓我們為您創建一個很好的範例，並向您展示如何編寫索引視圖的索引。
首先，讓我們創建一個名為 T1 的臨時表並在其中添加一些有意義的數據
讓我們創建一個首先生成隨機字元串的過程：
USE TEMPDB GO CREATE PROCEDURE [dbo].[SpGenerateRandomString] @sLength tinyint = 10, @randomString varchar(50) OUTPUT AS BEGIN SET NOCOUNT ON DECLARE @counter tinyint DECLARE @nextChar char(1) SET @counter = 1 SET @randomString = '' WHILE @counter <= @sLength BEGIN SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND())) IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96) BEGIN SELECT @randomString = @randomString + @nextChar SET @counter = @counter + 1 END END END現在讓我們將一些數據添加到 T1 並測試一些選擇：
use tempdb go create table t1 (i int not null identity(1,1) primary key clustered, the_name varchar (50) NOT NULL) declare @i int declare @randomString varchar(50) select @i = 1 while @i < 1008 begin exec SpGenerateRandomString 50, @randomString output insert into t1(the_name) values ( @randomString) select @i = @i + 1 end從 T1 執行一些選擇時：
在清除此查詢計劃的記憶體和此數據庫收集統計資訊 io 和時間後執行選擇，以便比較性能。
DECLARE @intDBID INT; SET @intDBID = (SELECT [dbid] FROM master.dbo.sysdatabases WHERE name = 'TempDB'); print @intDBID -- Flush the procedure cache for one database only DBCC FLUSHPROCINDB (@intDBID); SELECT cp.plan_handle, st.[text] FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE [text] LIKE N'%order by the_name desc%'; DBCC FREEPROCCACHE (0x060002002065C706F0BE22630400000001000000000000000000000000000000000000000000000000000000); set statistics io on set statistics time on select * from t1 where ([i]% 2) = 0 order by the_name desc這是生成的查詢計劃：
> 現在創建一個索引視圖：
這將在稍後用於執行相同的選擇並比較性能
--DROP VIEW VM_01 --GO create view vm_01 with schemabinding as select i, the_name from DBO.t1 where ([i]% 2) = 0 GO CREATE UNIQUE CLUSTERED INDEX PK_VM_01 ON DBO.VM_01 (THE_NAME,I) GO現在測試選擇和比較：
DECLARE @intDBID INT; SET @intDBID = (SELECT [dbid] FROM master.dbo.sysdatabases WHERE name = 'TempDB'); print @intDBID -- Flush the procedure cache for one database only DBCC FLUSHPROCINDB (@intDBID); SELECT cp.plan_handle, st.[text] FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE [text] LIKE N'%order by the_name desc%'; DBCC FREEPROCCACHE (0x060002002065C706F0BE22630400000001000000000000000000000000000000000000000000000000000000); set statistics io on set statistics time on select * from vm_01 WITH (NOEXPAND) where ([i]% 2) = 0 order by the_name desc set statistics io off set statistics time off
生成的查詢計劃：
正如它所證明的，使用索引視圖的查詢執行得更好，所以（因為我們不擔心這個例子中的寫入）我們想要保留索引。
這裡選擇顯示為視圖 VM_01 創建的聚集索引的索引創建腳本：
SET NOCOUNT ON SET DATEFORMAT DMY SET DEADLOCK_PRIORITY NORMAL; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED declare @TABLEID int declare @index_or_table_name varchar(256) select @TABLEID = OBJECT_ID('VM_01') , @index_or_table_name = 'vm_01' BEGIN TRY SELECT i.object_id, i.index_id, index_name=i.name, CASE WHEN I.is_primary_key = 1 THEN ' ALTER TABLE ' + QUOTENAME(Schema_name(T.Schema_id))+'.'+ QUOTENAME(T.name) + ' ADD CONSTRAINT ' + QUOTENAME(i.name) + ' PRIMARY KEY ' + I.type_desc + ' ( ' + KeyColumns + ' ) ' ELSE ' CREATE ' + CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + I.name + ' ON ' + QUOTENAME(Schema_name(T.Schema_id)) +'.'+ QUOTENAME(T.name) + ' ( ' + KeyColumns + ' ) ' + ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') + ISNULL(' WHERE '+I.Filter_definition,'') --sql2005 END -- case primary key or not + ' WITH ( ' + CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' + 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' + -- default value 'SORT_IN_TEMPDB = OFF ' + ',' + CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' + CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' + CASE WHEN I.is_primary_key = 1 THEN -- default value ' ONLINE = OFF ' + ',' ELSE -- default value ' ONLINE = OFF ' + ',' + -- default value ' DROP_EXISTING = ON ' + ',' END + CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' + CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' + DS.name + ' ] ' AS [CreateIndexScript] FROM sys.indexes I INNER JOIN ( SELECT Object_id ,Schema_id ,NAME FROM sys.tables UNION ALL SELECT Object_id ,Schema_id ,NAME FROM sys.views ) T ON T.Object_id = I.Object_id INNER JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid INNER JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + QUOTENAME(C.name) + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END FROM sys.index_columns IC1 JOIN sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 0 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id ORDER BY MAX(IC1.key_ordinal) FOR XML PATH('')), 1, 2, '') KeyColumns FROM sys.index_columns IC2 --WHERE IC2.Object_id = object_id('xtbUApplication') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id ) radhe3 ) radhe4 ON I.object_id = radhe4.object_id AND I.Index_id = radhe4.index_id JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id LEFT JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + QUOTENAME(C.name) FROM sys.index_columns IC1 JOIN sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 1 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id FOR XML PATH('')), 1, 2, '') IncludedColumns FROM sys.index_columns IC2 GROUP BY IC2.object_id ,IC2.index_id) tmp1 WHERE IncludedColumns IS NOT NULL ) tmp2 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id --------------------------------------------------------------------------------------------------- -- when @TABLEID = 0 -> all tables -- when @TABLEID = -1 -> only the index named = @index_or_table_name -- when @TABLEID has the object id of a particular table -> only shows that table --------------------------------------------------------------------------------------------------- WHERE 1 = CASE WHEN @TABLEID = 0 THEN 1 ELSE CASE WHEN @TABLEID = -1 AND UPPER(I.name) = @index_or_table_name THEN 1 ELSE CASE WHEN @TABLEID = I.object_id THEN 1 ELSE 0 END END END ORDER BY i.name END TRY BEGIN CATCH PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) + 'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER() AS VARCHAR), 'NO INFO') + CHAR(13) PRINT 'SEVERITY: ' + COALESCE(CAST ( ERROR_SEVERITY() AS VARCHAR), 'NO INFO') + CHAR(13) + 'STATE: ' + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO') + CHAR(13) PRINT 'PROCEDURE: ' + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO') AS VARCHAR), 'NO INFO') + CHAR(13) + 'LINE NUMBER: ' + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO') + CHAR(13) PRINT 'ERROR MESSAGE: ' PRINT CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO') AS NTEXT) END CATCH;當您執行上述腳本時，您會得到：
複製和粘貼向我們展示了腳本：
CREATE UNIQUE CLUSTERED INDEX PK_VM_01 ON [dbo].[vm_01] ( [the_name] ASC , [i] ASC ) WITH ( PAD_INDEX = OFF , FILLFACTOR = 100 , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , DROP_EXISTING = ON , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]還有一些錯誤需要修復，例如格式化和 drop_existing=on，但希望這會有所幫助。
