今天遇到个问题,与聚集索引有关,不知道大家有没有遇到过这样的情况,请大家不吝赐教.
表中有一nvarchar列,含有数字和字符.需要将该列中的全数字行转换为数值后进行筛选.
转换的方法是先使用isnumeric()= 1来判断每行是否为数值,然后再使用cast()转换.
当表中没有聚集索引的时候可以正常转换,而一旦加了聚集索引,则转换会出错,错误信息为:
服务器: 消息 8114，级别 16，状态 5，行 2
将数据类型 nvarchar 转换为 float 时出错。
以下是测试代码,系统环境为:
简体中文SqlServer2000sp4 + 简体中文WindowsServer2003sp1
IF OBJECT_ID(N'tbTest') IS NOT NULL
DROP TABLE tbTest
GO
----创建测试表
CREATE TABLE tbTest(ID int,姓名 nvarchar(20),部门代码 nvarchar(10))
insert into tbTest
select 1,N'张三',N'1000' union all
select 2,N'李四',N'1001' union all
select 3,N'王五',N'2001' union all
select 4,N'赵六',N'2002' union all
select 5,N'赵七',N'A3001' /*该行含有字符,并非全数字*/
GO
----查询(该查询能正常执行)
SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000
/*结果
ID 姓名 部门代码
----------- -------------------- ----------
2 李四 1001
3 王五 2001
4 赵六 2002
*/
GO
----！加入聚合索引
IF INDEXPROPERTY(object_id(N'tbTest'),N'IX_tbTest','IndexID') IS NOT NULL
DROP INDEX tbTest.IX_tbTest
GO
CREATE CLUSTERED INDEX IX_tbTest ON dbo.tbTest(ID)
GO
----查询(相同的查询代码,却因创建了聚合索引而抛出8114异常)
SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000
/*结果
ID 姓名 部门代码
----------- -------------------- ----------
2 李四 1001
3 王五 2001
4 赵六 2002
服务器: 消息 8114，级别 16，状态 5，行 2
将数据类型 nvarchar 转换为 float 时出错。
*/
