创建索引: CREATE UNIQUE CLUSTERED INDEX Index_ID ON View_PL_OK (ID_Num)错误提示: Server: Msg 1935, Level 16, State 1, Line 1 Cannot create index. Object 'PL_CV_TEMP' was created with the following SET options off: 'ANSI_NULLS.'.版本是SQL 2000。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PL_OK]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[PL_OK] GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GOCREATE VIEW dbo.PL_OK WITH SCHEMABINDING , VIEW_METADATA AS SELECT ID_Num, CVFILENAME, CVDATE, SRL, SA, SG, RANGE, ACODE, T, T85, T85SR1, T85SR2, T85DIG1, T85DIG2, T85DIG3, T85GR1, T85GR2, T60, T60SR1, T60SR2, T60DIG1, T60DIG2, T60DIG3, T25, T25SR1, T25SR2, T25DIG1, T25DIG2, T25DIG3, T15, T15SR1, T15SR2, T15DIG1, T15DIG2, T15DIG3, T40, T40SR1, T40SR2, T40DIG1, T40DIG2, T40DIG3, R1, R2, R3 FROM dbo.PL_CV_TEMP WHERE (((T85SR1 - T25SR1) / (T25SR2 - T25SR1) * 56 * 100) / (T85 - T25) BETWEEN - 0.3 AND 0.3) AND (((T85SR2 - T25SR2) / (T25SR2 - T25SR1) * 56 * 100) / (T85 - T25) BETWEEN - 0.6 AND 0.6) AND (((T60SR1 - T25SR1) / (T25SR2 - T25SR1) * 56 * 100) / (T60 - T25) BETWEEN - 0.3 AND 0.3) AND (((T60SR2 - T25SR2) / (T25SR2 - T25SR1) * 56 * 100) / (T60 - T25) BETWEEN - 0.6 AND 0.6) AND (((T15SR1 - T25SR1) / (T25SR2 - T25SR1) * 56 * 100) / (T15 - T25) BETWEEN - 0.3 AND 0.3) AND (((T15SR2 - T25SR2) / (T25SR2 - T25SR1) * 56 * 100) / (T15 - T25) BETWEEN - 0.6 AND 0.6) AND (((T40SR1 - T25SR1) / (T25SR2 - T25SR1) * 56 * 100) / (T40 - T25) BETWEEN - 0.3 AND 0.3) AND (((T40SR2 - T25SR2) / (T25SR2 - T25SR1) * 56 * 100) / (T40 - T25) BETWEEN - 0.6 AND 0.6) AND ((T85GR2 - T85GR1) / 8 * 100 BETWEEN - 0.2 AND 0.2) AND (T85DIG3 - T85 BETWEEN - 10 AND 8) AND (T60DIG3 - T60 BETWEEN - 10 AND 8) AND (T25DIG3 - T25 BETWEEN - 10 AND 8) AND (T15DIG3 - T15 BETWEEN - 10 AND 8) AND (T40DIG3 - T40 BETWEEN - 10 AND 8) AND (T85SR1 BETWEEN 1.9 AND 2.2) AND (T60SR1 BETWEEN 1.9 AND 2.2) AND (T25SR1 BETWEEN 1.9 AND 2.2) AND (T15SR1 BETWEEN 1.9 AND 2.2) AND (T40SR1 BETWEEN 1.9 AND 2.2) AND (ACODE = 'A') WITH CHECK OPTIONGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO就是这个语句创建视图。谢谢!!
问题: 1.你的表中的所有的float非精度类型,必须换成精度类型(decimal/numeric) 因为你的视图的条件中引用了这些字段,2.更换数据类型类型后,创建索引成功,我是在查询分析器中直接测试 你说"此语句我是在存储过程中调用",那就在查询分析器中创建存储过程 并且保证开启了相应的选项,即类似下面的这样创建存储过程: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create proc .....
CREATE UNIQUE CLUSTERED INDEX Index_ID ON View_PL_OK (ID_Num)错误提示:
Server: Msg 1935, Level 16, State 1, Line 1
Cannot create index. Object 'PL_CV_TEMP' was created with the following SET options off: 'ANSI_NULLS.'.版本是SQL 2000。
drop view [dbo].[PL_OK]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE VIEW dbo.PL_OK
WITH SCHEMABINDING , VIEW_METADATA
AS
SELECT ID_Num, CVFILENAME, CVDATE, SRL, SA, SG, RANGE, ACODE, T, T85, T85SR1,
T85SR2, T85DIG1, T85DIG2, T85DIG3, T85GR1, T85GR2, T60, T60SR1, T60SR2, T60DIG1,
T60DIG2, T60DIG3, T25, T25SR1, T25SR2, T25DIG1, T25DIG2, T25DIG3, T15, T15SR1,
T15SR2, T15DIG1, T15DIG2, T15DIG3, T40, T40SR1, T40SR2, T40DIG1, T40DIG2, T40DIG3,
R1, R2, R3
FROM dbo.PL_CV_TEMP
WHERE (((T85SR1 - T25SR1) / (T25SR2 - T25SR1) * 56 * 100) / (T85 - T25) BETWEEN - 0.3 AND
0.3) AND (((T85SR2 - T25SR2) / (T25SR2 - T25SR1) * 56 * 100) / (T85 - T25) BETWEEN
- 0.6 AND 0.6) AND (((T60SR1 - T25SR1) / (T25SR2 - T25SR1) * 56 * 100) / (T60 - T25) BETWEEN
- 0.3 AND 0.3) AND (((T60SR2 - T25SR2) / (T25SR2 - T25SR1) * 56 * 100) / (T60 - T25)
BETWEEN - 0.6 AND 0.6) AND (((T15SR1 - T25SR1) / (T25SR2 - T25SR1) * 56 * 100)
/ (T15 - T25) BETWEEN - 0.3 AND 0.3) AND (((T15SR2 - T25SR2) / (T25SR2 - T25SR1)
* 56 * 100) / (T15 - T25) BETWEEN - 0.6 AND 0.6) AND (((T40SR1 - T25SR1)
/ (T25SR2 - T25SR1) * 56 * 100) / (T40 - T25) BETWEEN - 0.3 AND 0.3) AND
(((T40SR2 - T25SR2) / (T25SR2 - T25SR1) * 56 * 100) / (T40 - T25) BETWEEN - 0.6 AND
0.6) AND ((T85GR2 - T85GR1) / 8 * 100 BETWEEN - 0.2 AND 0.2) AND
(T85DIG3 - T85 BETWEEN - 10 AND 8) AND (T60DIG3 - T60 BETWEEN - 10 AND 8) AND
(T25DIG3 - T25 BETWEEN - 10 AND 8) AND (T15DIG3 - T15 BETWEEN - 10 AND 8) AND
(T40DIG3 - T40 BETWEEN - 10 AND 8) AND (T85SR1 BETWEEN 1.9 AND 2.2) AND
(T60SR1 BETWEEN 1.9 AND 2.2) AND (T25SR1 BETWEEN 1.9 AND 2.2) AND
(T15SR1 BETWEEN 1.9 AND 2.2) AND (T40SR1 BETWEEN 1.9 AND 2.2) AND (ACODE = 'A')
WITH CHECK OPTIONGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO就是这个语句创建视图。谢谢!!
如果没有,将建表的脚本一并帖出
drop table [dbo].[PL_CV_TEMP]
GOCREATE TABLE [dbo].[PL_CV_TEMP] (
[ID_Num] [int] IDENTITY (1, 1) NOT NULL ,
[CVFILENAME] [char] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[CVDATE] [datetime] NULL ,
[SRL] [char] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[SA] [char] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[SG] [char] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[RANGE] [char] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[ACODE] [char] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[T] [char] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[T85] [float] NULL ,
[T85SR1] [float] NULL ,
[T85SR2] [float] NULL ,
[T85DIG1] [float] NULL ,
[T85DIG2] [float] NULL ,
[T85DIG3] [float] NULL ,
[T85GR1] [float] NULL ,
[T85GR2] [float] NULL ,
[T60] [float] NULL ,
[T60SR1] [float] NULL ,
[T60SR2] [float] NULL ,
[T60DIG1] [float] NULL ,
[T60DIG2] [float] NULL ,
[T60DIG3] [float] NULL ,
[T25] [float] NULL ,
[T25SR1] [float] NULL ,
[T25SR2] [float] NULL ,
[T25DIG1] [float] NULL ,
[T25DIG2] [float] NULL ,
[T25DIG3] [float] NULL ,
[T15] [float] NULL ,
[T15SR1] [float] NULL ,
[T15SR2] [float] NULL ,
[T15DIG1] [float] NULL ,
[T15DIG2] [float] NULL ,
[T15DIG3] [float] NULL ,
[T40] [float] NULL ,
[T40SR1] [float] NULL ,
[T40SR2] [float] NULL ,
[T40DIG1] [float] NULL ,
[T40DIG2] [float] NULL ,
[T40DIG3] [float] NULL ,
[R1] [char] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[R2] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[R3] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
) ON [PRIMARY]
此语句我是在存储过程中调用。Help!
1.你的表中的所有的float非精度类型,必须换成精度类型(decimal/numeric)
因为你的视图的条件中引用了这些字段,2.更换数据类型类型后,创建索引成功,我是在查询分析器中直接测试
你说"此语句我是在存储过程中调用",那就在查询分析器中创建存储过程
并且保证开启了相应的选项,即类似下面的这样创建存储过程: SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc .....