sqlserver 建立索引视图各问题！

最新推荐文章于 2022-04-29 09:57:18 发布

天水宇

最新推荐文章于 2022-04-29 09:57:18 发布

阅读量1.7k

点赞数

--我的 tab 表有 60398 条数据,大家可以用自己的任意表测试
SELECT * FROM tab
GO
--新建表MYTABLE,带序号
IF object_id(N'[dbo].[MYTABLE]') IS NOT NULL drop table MYTABLE
SELECT IDENTITY(INT,1,1) ID,* INTO MYTABLE FROM tab
GO
SELECT * FROM MYTABLE
--CREATE CLUSTERED INDEX PRIMARYKEY ON MYTABLE(ID)
--if exists(select * from sysindexes where id=object_id('MYTABLE') and name='PRIMARYKEY')
--建立分割表,把MYTABLE中的数据划分到两个表中,用凉表做测试
IF object_id(N'[dbo].[MYTABLE_TOP50PERTCENT]') IS NOT NULL drop table MYTABLE_TOP50PERTCENT
IF object_id(N'[dbo].[MYTABLE_LAST50PERTCENT]') IS NOT NULL drop table MYTABLE_LAST50PERTCENT
SELECT TOP 50 PERCENT * INTO MYTABLE_TOP50PERTCENT FROM MYTABLE
SELECT * INTO MYTABLE_LAST50PERTCENT FROM MYTABLE WHERE ID NOT IN (SELECT ID FROM MYTABLE_TOP50PERTCENT)
GO
--建立联合视图(这个建法不正确,等下改正)
IF object_id(N'[dbo].[VW_MYTABLE_ALL]') IS NOT NULL DROP VIEW VW_MYTABLE_ALL
GO
CREATE VIEW dbo.VW_MYTABLE_ALL
--WITH SCHEMABINDING /*如添加WITH SCHEMABINDING,则表必须带架构名,如dbo.MYTABLE_TOP50PERTCENT*/
AS
SELECT * FROM MYTABLE_TOP50PERTCENT
UNION ALL
SELECT * FROM MYTABLE_LAST50PERTCENT
GO
--查询视图,对表扫描
SELECT * FROM VW_MYTABLE_ALL
--对分割表创建索引,对视图的查询会转向表的查询
if exists(select * from sysindexes where id=object_id('MYTABLE_TOP50PERTCENT') and name='PRIMARYKEY1')
drop INDEX MYTABLE_TOP50PERTCENT.PRIMARYKEY1
if exists(select * from sysindexes where id=object_id('MYTABLE_LAST50PERTCENT') and name='PRIMARYKEY2')
drop INDEX MYTABLE_LAST50PERTCENT.PRIMARYKEY2
CREATE CLUSTERED INDEX PRIMARYKEY1 ON MYTABLE_TOP50PERTCENT(ID)
CREATE CLUSTERED INDEX PRIMARYKEY2 ON MYTABLE_LAST50PERTCENT(ID)
--查询视图,此时使用索引查询
SELECT * FROM VW_MYTABLE_ALL
--删除表的索引,建立视图索引
if exists(select * from sysindexes where id=object_id('MYTABLE_TOP50PERTCENT') and name='PRIMARYKEY1')
drop INDEX MYTABLE_TOP50PERTCENT.PRIMARYKEY1
if exists(select * from sysindexes where id=object_id('MYTABLE_LAST50PERTCENT') and name='PRIMARYKEY2')
drop INDEX MYTABLE_LAST50PERTCENT.PRIMARYKEY2
CREATE UNIQUE CLUSTERED INDEX IND_VW_MYTABLE ON VW_MYTABLE_ALL(ID ASC)
go
--错误:无法对视图 'VW_MYTABLE_ALL' 创建索引，因为该视图未绑定到架构
--解决:因此之前创建的视图应该为这样,将视图绑定到架构
IF object_id(N'[dbo].[VW_MYTABLE_ALL]') IS NOT NULL DROP VIEW VW_MYTABLE_ALL
GO
CREATE VIEW dbo.VW_MYTABLE_ALL
WITH SCHEMABINDING
AS
SELECT * FROM dbo.MYTABLE_TOP50PERTCENT
UNION ALL
SELECT * FROM dbo.MYTABLE_LAST50PERTCENT
GO
--错误:在绑定到架构的对象中不允许使用语法 '*'。
--解决:必须把视图中的查询列名写出，如下
IF object_id(N'[dbo].[VW_MYTABLE_ALL]') IS NOT NULL DROP VIEW VW_MYTABLE_ALL
GO
CREATE VIEW dbo.VW_MYTABLE_ALL
WITH SCHEMABINDING
AS
SELECT ID,ProductKey,CustomerKey,OrderDateKey,UnitPrice,UnitPriceDiscountPct FROM dbo.MYTABLE_TOP50PERTCENT
UNION ALL
SELECT ID,ProductKey,CustomerKey,OrderDateKey,UnitPrice,UnitPriceDiscountPct FROM dbo.MYTABLE_LAST50PERTCENT
GO
--创建视图索引
CREATE UNIQUE CLUSTERED INDEX IND_VW_MYTABLE ON dbo.VW_MYTABLE_ALL(ID ASC) ON ps_OrderDateKey(id)
go
/*
!!仍有错误:
无法对视图 'Sales_DW.dbo.VW_MYTABLE_ALL' 创建索引，因为其中包含一个或多个 UNION、INTERSECT 或 EXCEPT 运算符。
如果将查询作为原始视图的 UNION、INTERSECT 或 EXCEPT 运算符的输入，请考虑为每个这样的查询创建一个单独的索引视图。
*/
/*==========================================================================================*/
/*==========================================================================================*/
--白忙一场!!视图索引只对单个查询建立,一下以MYTABLE看看
IF object_id(N'[dbo].[VW_MYTABLE]') IS NOT NULL DROP VIEW VW_MYTABLE
GO
CREATE VIEW dbo.VW_MYTABLE
WITH SCHEMABINDING
AS
SELECT ID,ProductKey,CustomerKey,OrderDateKey,UnitPrice,UnitPriceDiscountPct FROM dbo.MYTABLE
GO
if exists(select * from sysindexes where id=object_id('VW_MYTABLE') and name='IND_MYTABLE')
drop INDEX VW_MYTABLE.IND_MYTABLE
CREATE UNIQUE CLUSTERED INDEX IND_MYTABLE ON dbo.VW_MYTABLE(ID ASC)
go
SELECT * FROM VW_MYTABLE
SELECT * FROM MYTABLE

[sql] view plain copy

<strong><span style="font-size:14px;">--网上查到的各约束规则</span></strong>
/*
一个标准视图转换为一个索引视图必须遵守以下规则：
A．视图必须使用With Schemabinding选项来创建；
如果创建视图时没有with Schemabinding，试图创建视图时就会报错：……因为该视图未绑定到架构
B．在这个视图中不能使用其他视图、导出表、行集函数或自查询，也就是说只能使用表；
C．视图所用到的基本表必须和视图属于同一个所有者；
D．视图只能链接同一个数据库中的表；
E．视图不能包含一个外部链接或自链接，也就是说在链接表时只能使用INNER JOIN并且INNER JOIN前后不能使同一个表，不能使用LEFT（RIGHT） JOIN 或者 LEFT （RIGHT） OUTER JOIN ；
F．视图不能包含UNION子句、TOP子句、ORDER BY子句、Having子句、Rollup子句、Cube子句、compute子句、Compute By子句或Distinct关键字；
G．视图不允许使用某些集合函数，如：Count（*）可以使用count_big（*）代替、avg()、max()、min()、stdev()、stdevp()、var()或varp()等；
H．视图不能使用Select * 这样的语句，也就是说视图的所有字段都必须显示指定；
I．视图不能包含Text、ntext、image类型的列；
J．如果视图包含一个Group By子句，那么他必须在Select列中包含count_big(*)；
K．视图中的所有标和用户自定义的函数都必须使用两段式名来引用，即所有者.表或函数名称；
L．所有的基本表和视图都必须使用 Set Ansi_Nulls On 创建；
M．在创建索引时或创建索引后执行IUD时，必须显示或隐式地执行：
Set ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
N．索引视图只有在SQL Server2000的企业版或开发版或者更高的版本中才能创建。
在一个表上创建了索引视图后，对其执行delete操作时报错：DELETE 失败，
因为下列 SET 选项的设置不正确: 'CONCAT_NULL_YIELDS_NULL, ANSI_PADDING,ARITHABORT'，删除这个视图问题就解决了
*/