After a Developer altered an Indexed View to add a comment, the clustered index that was created on it was dropped with no warning.
This caused a failure as there are a couple of procedures that have the WITH (NOEXPAND) hint.
I have SSMS set up currently so that right-clicking the VIEW and selecting SCRIPT VIEW as, CREATE TO includes the index definition.
Is there a way to script out indexes on VIEWS for ALTER to?
O.K. lets create a nice example for you and show you how to script the index of your indexed view.
first, lets create a temp table called T1 and add some meaningful data into it
lets create a procedure that generates random strings first:
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
now lets add some data into T1 and test some selects:
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
when running some selects from T1:
running selects after clearing the cache for this query plan and this database collecting statistics io and time, in order to compare performance.
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
This is the query plan generated:
> Now creating an indexed view:
this will be used later on to run the same select and compare the performance
--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
Now testing the select and comparing:
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
the query plan generated:
as it was demonstrated the query using the indexed view performed much better, so (because we don't worry about the writes in this example) we want to keep the index.
Here a select to show you the index creation script for the clustered index created to the view 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;
when you run the above script you get:
and a copy and paste shows us the script:
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 ]
there are still some bugs to fix, like formatting, and the drop_existing=on, but hopefully this will help.
CREATE syntax for the index. Would this mean that there is no way to add the CREATE script to the ALTER index script?