索引视图DEMO1
--use tempdb ----------------------在创建视图和所有底层表时,必须打开ANSI_NULLS以及QUOTED_IDENTIFIER选项 --SET ANSI_NULLS ON --SET QUOTED_IDENTIFIER ON ----------------------创建表 --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_PADDING ON --GO --CREATE TABLE [dbo].[PersonTenMillion]( -- [Id] [uniqueidentifier] NOT NULL, -- [Age] [int] NULL, -- [UserName] [varchar](50) NULL, -- [CreateTime] [datetime] NULL, -- CONSTRAINT [PK_PersonTenMillion] PRIMARY KEY CLUSTERED --( -- [Id] ASC --)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] --) ON [PRIMARY] --GO --SET ANSI_PADDING OFF --GO ----------------------删除所有数据 --truncate TABLE [PersonTenMillion] ----------------------创建测试数据 --DECLARE @i int = 0; --WHILE @i<2000000 --BEGIN -- INSERT INTO [PersonTenMillion] values (NEWID(),cast(ceiling(rand() * 100) as int),'username',GETDATE()-RAND()*100) -- SET @i=@i+1 --END --GO ----------------------创建索引视图 --CREATE VIEW PersonAge_vw --WITH SCHEMABINDING --AS --SELECT Age,COUNT_BIG(*) AS CountAge FROM dbo.PersonTenMillion --GROUP BY Age ----------------------为视图创建索引 --CREATE UNIQUE CLUSTERED INDEX ivPersonAge --ON PersonAge_vw(Age) ----------------------物理表测试 SET STATISTICS TIME ON --开启统计时间 SELECT Age,COUNT(Age) FROM PersonTenMillion GROUP BY Age ORDER BY Age ----------------------索引视图测试 SELECT * FROM PersonAge_vw