Incluir scripts para índices em Alter Views

OK, vamos criar um bom exemplo para você e mostrar como fazer o script do índice de sua exibição indexada.

primeiro, vamos criar uma tabela temporária chamada T1 e adicionar alguns dados significativos a ela

vamos criar um procedimento que gere strings aleatórias primeiro:

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

agora vamos adicionar alguns dados em T1 e testar algumas seleções:

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 

ao executar algumas seleções de T1:

running selects depois de limpar o cache para este plano de consulta e este banco de dados coletando estatísticas io e tempo, a fim de comparar o desempenho.

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

Este é o plano de consulta gerado:

> Agora, criando uma exibição indexada:

isso será usado mais tarde para executar a mesma seleção e comparar o desempenho

--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

Agora testando o select e comparando:

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

o plano de consulta gerado:

como foi demonstrado que a consulta usando a visão indexada teve um desempenho muito melhor, então (porque não nos preocupamos com as gravações neste exemplo) queremos manter o índice.

Aqui, selecione para mostrar o script de criação de índice para o índice clusterizado criado para a exibição 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;

quando você executa o script acima, obtém:

e um copiar e colar nos mostra o 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 ]

ainda há alguns bugs para corrigir, como formatação e drop_existing=on, mas espero que isso ajude.