create unique clustered index clu_name on View_1(a)
无法在该视图上建立索引,因为该视图未绑定到框架是不是视图不可以建立索引?
无法在该视图上建立索引,因为该视图未绑定到框架是不是视图不可以建立索引?
解决方案 »
- 关于这个SQL语句的写法
- 菜鸟问题:varchar(max)及字符大小
- 新手求教一个sql语句,在线等
- 以下两个SQL语句有什么区别?那个好?
- 表中有一列的数据有重复的数据,能否把这个表变成另外的形式
- 请教如何用利用现有的SQL函数得到某月的第一天属于第几周的值
- 安装sql server时出的一个问题,不知如何是好
- SQL Server中把各个表UNION在一起的效率问题?
- SQL Server 7.0 安装时的故障
- SP_addlinkedserve 在sqlserver如何连接oracle数据库
- reporting services报表管理器上载文件失败
- sql多个关键字搜索,单个关键字出现的次数排序
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
•索引视图涉及的基本表必须ANSI_NULLS设置为ON
•索引视图必须设置ANSI_NULLS和QUOTED_INDETIFIER为ON
•索引视图只能引用基本表
•SCHEMABINDING必须设置
•定义索引视图时必须使用Schema.ViewName这样的全名
•索引视图中不能有子查询
•avg,max,min,stdev,stdevp,var,varp这些聚合函数不能用
--1.1创建索引视图
create view dbo.v_index_wcT
with schemabinding --必须要写
as
select wcid,
wcV,
wcDate
from dbo.wcT --必须要有架构,也就是:dbo.对象
go
--2.1在索引视图上创建唯一聚集索引
create unique clustered index idx_v_index_wcT
on dbo.v_index_wcT(wcid)
--2.2创建非聚集索引
create nonclustered index idx_v_index_wcT_wcV
on dbo.v_index_wcT(wcV)
GO
/****** 对象: View [dbo].[V_s] 脚本日期: 11/13/2013 14:55:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_s]
WITH SCHEMABINDING
AS
SELECT dbo.Tb_rq0001.RQ, V.a, V.e, V.f, V.g, V.h, V.perday, V.j_, V.dd, V.duty_kind, V.m_id
FROM dbo.Tb_rq0001 CROSS JOIN
(SELECT ID, a, b, c, d, e, f, g, h, i, j, m, m_id, daynumbers, perday, j_, dd, duty_kind
FROM dbo.V AS V_1) AS V
WHERE (dbo.Tb_rq0001.RQ BETWEEN V.c AND V.dd)
你的视图里还引用了其他的视图,比如dbo.V,肯定是不行的,只能引用基本表
DROP TABLE test1
CREATE TABLE test1(id int)IF OBJECT_ID('test2','u') IS NOT NULL
DROP TABLE test2
CREATE TABLE test2(id int)
go
IF OBJECT_ID('t1','v') IS NOT NULL
DROP VIEW t1
go
CREATE VIEW t1
WITH SCHEMABINDING
AS
SELECT a.id
FROM dbo.test1 a INNER JOIN dbo.test2 b ON a.id=b.id
GO
IF OBJECT_ID('t2','V') IS NOT NULL
DROP VIEW t2
go
CREATE VIEW t2
WITH SCHEMABINDING
AS
SELECT test1.id
FROM dbo.test1 INNER JOIN dbo.t1 ON test1.id=t1.id
GOcreate unique clustered index clu_name on t1(id)
create unique clustered index clu_name on t2(id)
/*
Msg 1937, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.t2' because it references another view 'dbo.t1'. Consider expanding referenced view's definition by hand in indexed view definition.
*/
= '星期六' THEN d - 1 WHEN datename(weekday, c) = '星期六' AND datename(weekday, d) = '星期日' AND e = 'PM' AND
g = 'AM' THEN d - 1 WHEN datename(weekday, d) = '星期六' AND c <> d AND e = 'PM' AND g = 'AM' THEN d - 1 WHEN datename(weekday, c)
= '星期日' AND datename(weekday, d) = '星期一' THEN d - 1 WHEN datename(weekday, c) = '星期六' AND datename(weekday, d)
= '星期一' THEN d - 1 WHEN c <> d AND e = 'PM' AND g = 'AM' THEN d - 1 ELSE d END) + 1
@@ERROR FORMATMESSAGE NEWID
@@IDENTITY GETANSINULL PATINDEX
@@ROWCOUNT GETDATE PERMISSIONS
@@TRANCOUNT GetUTCDate SESSION_USER
APP_NAME HOST_ID STATS_DATE
CHARINDEX HOST_NAME SYSTEM_USER
CURRENT_TIMESTAMP IDENT_INCR TEXTPTR
CURRENT_USER IDENT_SEED TEXTVALID
DATENAME IDENTITY USER_NAME
这些是非确定性函数,如果视图用到这些函数,不能创建索引