Skip to main content
replaced http://stackoverflow.com/ with https://stackoverflow.com/
Source Link
added 470 characters in body
Source Link
Marcello Miorelli
  • 16.6k
  • 53
  • 170
  • 311

now lets add some data into T1 and test some selects:now lets add some data into T1 and test some selects:

whenwhen running some selects from T1:

running some selects from T1:after clearing the cache for this query plan and this database collecting statistics io and time, in order to compare performance.

This is the query plan I get:This is the query plan generated:

enter image description here Now creating an indexed view:

> Now creating an indexed view:

this will be used later on to run the same select and compare the performance

the query plan: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.

when you run that you get:when you run the above script you get:

and a copy and paste shows the script: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 ] 

now lets add some data into T1 and test some selects:

when running some selects from T1:

This is the query plan I get:

enter image description here Now creating an indexed view:

the query plan:

when you run that you get:

and a copy and paste shows 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 ] 

now lets add some data into T1 and test some selects:

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.

This is the query plan generated:

enter image description here

> Now creating an indexed view:

this will be used later on to run the same select and compare the performance

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.

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 ]
Source Link
Marcello Miorelli
  • 16.6k
  • 53
  • 170
  • 311

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:

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 I get:

enter image description here

enter image description here Now creating an indexed view:

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

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 that you get:

enter image description here

and a copy and paste shows 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.