1

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?

2
  • Have you looked at this script ?
    – Kin Shah
    Apr 21 '16 at 16:30
  • @Kin Looks pretty good, thanks. Not sure why it's a cursor though...?
    – Shaneis
    Apr 21 '16 at 20:34
2

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:

enter image description here

enter image description here

> 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

enter image description here

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.

enter image description here

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:

enter image description here

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.

3
  • V. nice script, especially the random data generation and removing a specific plan from the cache. However, this seems to be a script for generating the CREATE syntax for the index. Would this mean that there is no way to add the CREATE script to the ALTER index script?
    – Shaneis
    Apr 25 '16 at 9:40
  • @Shaneis the script above generates the create index successfully. when you alter the view you will need to re-create the index. alternatively you can use the ssms options. tools->options->SQL Server Object Exprorer-> Scripting-> set the option Generate script for dependent objects to true. Then on the ssms when you right click on the view-> create-> it includes the index creation as well as the view creation. Apr 25 '16 at 11:27
  • 1
    Thanks Marcello, I realise that there's no way to include the Index from the ALTER to option of SSMS. Your answerm including the SSMS options comment, fits for everything else though.
    – Shaneis
    Apr 26 '16 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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