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