索引视图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