首页新闻找找看学习计划

各位,有空帮我看看视图索引的问题

0
悬赏园豆:5 [已关闭问题] 关闭于 2015-11-19 13:25

use mlk;
GO
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 ('dbo.mtlevel1', 'view') IS NOT NULL
DROP VIEW dbo.mtlevel1;
GO
CREATE VIEW dbo.mtlevel1
WITH SCHEMABINDING AS
SELECT
COUNT_BIG(*) AS TotalNumber,
Sum(dbo.TT_MT_LOG.MT_TOTAL) AS Demolition,
Sum(CASE WHEN dbo.TT_MT_LOG.MT_STATE=2 THEN 1 else 0 END) AS SendSuccess,
Sum(CASE WHEN dbo.TT_MT_LOG.MT_STATE=3 THEN 1 else 0 END) AS SendFailed,
Sum(CASE WHEN dbo.TT_MT_LOG.MT_STATE=4 THEN 1 else 0 END) AS ReceiveSuccess,
Sum(CASE WHEN dbo.TT_MT_LOG.MT_STATE=5 THEN 1 else 0 END) AS ReceiveFailed,
Sum(CASE WHEN dbo.TT_MT_LOG.MT_STATE=8 THEN 1 else 0 END) AS ReceiveNoState,
dbo.TT_MT_LOG.APP_PARAMETER1 AS BizCode,
CONVERT(datetime,CONVERT (CHAR (10),dbo.TT_MT_LOG.SEND_TIME,120)) AS Days

FROM
dbo.TT_MT_LOG where MT_TOTAL is not null
GROUP BY
dbo.TT_MT_LOG.APP_PARAMETER1,
CONVERT(datetime,CONVERT (CHAR (10),dbo.TT_MT_LOG.SEND_TIME,120))
GO
CREATE UNIQUE CLUSTERED INDEX mt_index ON mtlevel1(Days DESC,BizCode);
GO

创建视图索引报错了,不太懂这个,原因我不知道啊,大神帮我看看。

错误:

[SQL]
CREATE UNIQUE CLUSTERED INDEX mt_index ON mtlevel1(Days DESC,BizCode)
[Err] 42000 - [SQL Server]Column 'Days' in view 'mtlevel1' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

 

狗霸人间的主页 狗霸人间 | 初学一级 | 园豆:4
提问于:2015-11-16 15:25
< >
分享
所有回答(2)
0

改创建非聚集索引吧。

Firen | 园豆:5483 (大侠五级) | 2015-11-17 11:47
0

CONVERT(datetime,CONVERT (CHAR (10),dbo.TT_MT_LOG.SEND_TIME,120),120)

狗霸人间 | 园豆:4 (初学一级) | 2015-11-19 13:25
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册