存储过程中创建视图索引

[tangzaicheng]

大家好，我用的是存储过程，在存储过程中有个查询语句，因为数据量大所以查询速度较慢。想见索引，但在查询语句中所用的表是事先建好的视图，请问如何建立索引，谢谢。
存储过程中的查询语句：
declare @sql nvarchar(500)
set @sql=N'SELECT CatalogueType,CatalogueTypeName,COUNT(CatalogueType) as BookClassCount,
cast(COUNT(CatalogueType) * 100 as float) / cast((SELECT COUNT(*) AS Expr1
FROM ' + @LibName+'.dbo.vBookTypeCatalogue2 WHERE (BookType = 0)) as float) as BookBL,SUM(Price)
as MoneyCount FROM '+ @LibName+ '.dbo.vBookTypeCatalogue2 WHERE (BookType = 0) GROUP BY
CatalogueType,CatalogueTypeName'


查询语句中的vBookTypeCatalogue2是事先建立好的视图。谢谢。

[tangzaicheng]

创建视图语句为：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vBookTypeCatalogue2]
AS
SELECT DISTINCT
dbo.vBookCatalogue.ID, dbo.vBookType.CatalogueType,
dbo.vBookType.CatalogueTypeName, dbo.vBookType.TypeName,
dbo.vBookType.BookType, dbo.vBookCatalogue.RegTime,
dbo.vBookCatalogue.LibraryID, dbo.vBookCatalogue.ClassSecondIndex,
dbo.vBookCatalogue.Price, dbo.vBookCatalogue.CID
FROM dbo.vBookCatalogue INNER JOIN
dbo.vBookType ON dbo.vBookCatalogue.ID = dbo.vBookType.ID
WHERE (dbo.vBookCatalogue.State IN (1, 2))

[--小F--]

---索引视图

索引视图是具体化的视图

--创建索引视图
create view 视图名 with schemabinding 
as
select 语句
go

---创建索引视图需要注意的几点
1. 创建索引视图的时候需要指定表所属的架构
--错误写法
create view v_f with schemabinding 
as
select
   a.a,a.b,b.a,b.b
from
   a join b 
on
   a.id=b.id
go

---正确写法：
create view v_f with schemabinding 
as
select
   a.a,a.b,b.a,b.b
from
   dbo.a join dbo.b 
on
   a.id=b.id
go


2.在创建索引视图的select语句时，不使用*,必须指定具体的列名
--错误写法
create view v_f with schemabinding 
as
select
   *
from
   dbo.a join dbo.b 
on
   a.id=b.id
go

---正确写法
create view v_f with schemabinding 
as
select
   a.a,a.b,b.a,b.b
from
   dbo.a join dbo.b 
on
   a.id=b.id
go

3.在创建索引视图的select 语句中，不能存在重复的列名，这个不举例了

4. 只能为索引视图创建唯一聚集索引
--正确的写法
create unique clustered index ix_uniquetb on v_tb
go

--错误的写法 
create clustered index ix_uniquetb on v_tb
go

[AcHerat]

创建索引视图！F姐给的很清楚了。

[老潘]

#2楼正解

[SQL77]

F1 联机帮助，索引--查询索引视图，有相关的约束和建法

[cd731107]

#2楼说的已经很清楚了