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 时出错。
*/
SELECT * FROM tbTest WHERE case when isnumeric(部门代码) = 1 then cast(部门代码 as float) else 0 end > 1000
SELECT * FROM tbTest WHERE ISNULL(NULLIF(ISNUMERIC(部门代码),1),CAST(部门代码 AS FLOAT)) >1000
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------------------------------------------- -------- ------------------------------ -------- ------------------------
SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000 4 1 0 NULL NULL 1 NULL 1.0031105 NULL NULL NULL 3.7663501E-2 NULL NULL SELECT 0 NULL
|--Clustered Index Scan(OBJECT:([CSDN_TEST].[dbo].[tbTest].[IX_tbTest]), WHERE:(Convert([tbTest].[部门代码])>1000 AND isnumeric(Convert([tbTest].[部门代码]))=1)) 4 3 1 Clustered Index Scan Clustered Index Scan OBJECT:([CSDN_TEST].[dbo].[tbTest].[IX_tbTest]), WHERE:(Convert([tbTest].[部门代码])>1000 AND isnumeric(Convert([tbTest].[部门代码]))=1) [tbTest].[部门代码], [tbTest].[姓名], [tbTest].[ID] 1.0031105 3.7578501E-2 8.3999999E-5 45 3.7662499E-2 [tbTest].[部门代码], [tbTest].[姓名], [tbTest].[ID] NULL PLAN_ROW 0 1.0
（所影响的行数为 2 行）
if(Request["xx"]!=null && Request["xx"]=="aa")
{
//do something
}
SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000
WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000
