索引视图是具体化的视图--创建索引视图 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 go3.在创建索引视图的select 语句中,不能存在重复的列名,这个不举例了4. 只能为索引视图创建唯一聚集索引 --正确的写法 create unique clustered index ix_uniquetb on v_tb go--错误的写法 create clustered index ix_uniquetb on v_tb go
create view v_name with schemabinding as select * from dbo.tb
------在试图上创建索引 create view v_test with schemabinding as select id from dbo.test --建立视图索引的前提有四个,都要在创建视图时满足 --1、要用with schemabinding绑定schema --2、视图中用到的表格式要满足schema.test,如dbo.test,如果写成test,则报错 --3、select中要指定列名,如id,不能使用* --4、视图上要建立索引,先要建立唯一的聚集索引,只有建立了唯一的聚集索引才能建立非聚集索引 详细可以参考http://blog.csdn.net/yubofighting/article/details/6834692
视图索引,select 后不能用*吧,要有具体的列名
with schemabinding
看看索因视图 创建的条件. 要建立索引的视图的名称。必须使用 SCHEMABINDING 定义视图才能在视图上创建索引。视图定义也必须具有确定性。如果选择列表中的所有表达式、WHERE 和 GROUP BY 子句都具有确定性,则视图也具有确定性。而且,所有键列必须是精确的。只有视图的非键列可能包含浮点表达式(使用 float 数据类型的表达式),而且 float 表达式不能在视图定义的其它任何位置使用。若要在确定性视图中查找列,请使用 COLUMNPROPERTY 函数(IsDeterministic 属性)。该函数的 IsPrecise 属性可用来确定键列是否精确。必须先为视图创建唯一的聚集索引,才能为该视图创建非聚集索引。在 SQL Server 企业版或开发版中,查询优化器可使用索引视图加快查询的执行速度。要使优化程序考虑将该视图作为替换,并不需要在查询中引用该视图。在创建索引视图或对参与索引视图的表中的行进行操作时,有 7 个 SET 选项必须指派特定的值。SET 选项 ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER、ANSI_NULLS、ANSI_PADDING 和 ANSI_WARNING 必须为 ON。SET 选项 NUMERIC_ROUNDABORT 必须为 OFF。
參照語法和例子 http://technet.microsoft.com/zh-cn/library/ms191432%28SQL.90%29.aspxUSE AdventureWorks; GO --Set the options to support indexed views. SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON; GO --Create view with schemabinding. IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL DROP VIEW Sales.vOrders ; GO CREATE VIEW Sales.vOrders WITH SCHEMABINDING AS SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o WHERE od.SalesOrderID = o.SalesOrderID GROUP BY OrderDate, ProductID; GO --Create an index on the view. CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.vOrders (OrderDate, ProductID); GO --This query can use the indexed view even though the view is --not specified in the FROM clause. SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, OrderDate, ProductID FROM Sales.SalesOrderDetail AS od JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID AND ProductID BETWEEN 700 and 800 AND OrderDate >= CONVERT(datetime,'05/01/2002',101) GROUP BY OrderDate, ProductID ORDER BY Rev DESC; GO --This query can use the above indexed view. SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev FROM Sales.SalesOrderDetail AS od JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID AND DATEPART(mm,OrderDate)= 3 AND DATEPART(yy,OrderDate) = 2002 GROUP BY OrderDate ORDER BY OrderDate ASC; GO
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
go3.在创建索引视图的select 语句中,不能存在重复的列名,这个不举例了4. 只能为索引视图创建唯一聚集索引
--正确的写法
create unique clustered index ix_uniquetb on v_tb
go--错误的写法
create clustered index ix_uniquetb on v_tb
go
as
select * from dbo.tb
create view v_test
with schemabinding
as
select id from dbo.test
--建立视图索引的前提有四个,都要在创建视图时满足
--1、要用with schemabinding绑定schema
--2、视图中用到的表格式要满足schema.test,如dbo.test,如果写成test,则报错
--3、select中要指定列名,如id,不能使用*
--4、视图上要建立索引,先要建立唯一的聚集索引,只有建立了唯一的聚集索引才能建立非聚集索引
详细可以参考http://blog.csdn.net/yubofighting/article/details/6834692
要建立索引的视图的名称。必须使用 SCHEMABINDING 定义视图才能在视图上创建索引。视图定义也必须具有确定性。如果选择列表中的所有表达式、WHERE 和 GROUP BY 子句都具有确定性,则视图也具有确定性。而且,所有键列必须是精确的。只有视图的非键列可能包含浮点表达式(使用 float 数据类型的表达式),而且 float 表达式不能在视图定义的其它任何位置使用。若要在确定性视图中查找列,请使用 COLUMNPROPERTY 函数(IsDeterministic 属性)。该函数的 IsPrecise 属性可用来确定键列是否精确。必须先为视图创建唯一的聚集索引,才能为该视图创建非聚集索引。在 SQL Server 企业版或开发版中,查询优化器可使用索引视图加快查询的执行速度。要使优化程序考虑将该视图作为替换,并不需要在查询中引用该视图。在创建索引视图或对参与索引视图的表中的行进行操作时,有 7 个 SET 选项必须指派特定的值。SET 选项 ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER、ANSI_NULLS、ANSI_PADDING 和 ANSI_WARNING 必须为 ON。SET 选项 NUMERIC_ROUNDABORT 必须为 OFF。
http://technet.microsoft.com/zh-cn/library/ms191432%28SQL.90%29.aspxUSE AdventureWorks;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev,
OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
AND ProductID BETWEEN 700 and 800
AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO