SQL Server 多表联合查询关联时nvarchar转float类型转换报错求助

• 报错位置和实际触发转换的位置不匹配：SQL Server查询优化器会对关联条件做谓词下推，你看到的select t1.pod行触发的错误，实际是优化器把关联时的隐式转换逻辑提前推到了源表扫描环节，不是当前select行执行时才触发的
• 隐式转换的方向不受select层代码控制：float类型的优先级高于nvarchar，只要关联字段一端是float、一端是nvarchar，SQL Server会固定把nvarchar值转换成float做等值比较，不管你在select层手动给字段套多少层CAST(... AS NVARCHAR)，都不会改变这个转换方向
• 现有ISNUMERIC判断存在漏洞：该函数会把带科学计数法、带小数点、带货币符号的字符串判定为“数值”，这类值在做float比较时要么精度丢失匹配不上，要么直接触发转换失败
• 你之前手动把t1.pod转nvarchar的操作无效，本质是临时表构建时，源offcoms的float字段没有经过强制的格式规整，直接转成nvarchar后留存了带特殊格式的脏值，关联时仍然会触发反向的float转换逻辑

• 替换offcoms表float类型MPXN的转换逻辑，不再依赖ISNUMERIC判断后直接转字符串：使用两层嵌套转换先消除float存储带来的格式问题，即先把float值转成不带小数的大整数类型，再转成字符串存储，示例代码：
  CAST(CAST(offcoms.mpxn AS DECIMAL(18,0)) AS NVARCHAR(20))
  这种方式可以从根源上消除float转字符串时自动生成的科学计数法、多余小数位问题
• 构建临时表时强制统一MPXN字段的类型：不要让SQL Server自动推导临时表的字段类型，手动把统一后的MPXN字段定义为NVARCHAR(20)，和DCC表的MPXN存储类型保持一致
• 关联health表时消除反向隐式转换：关联条件两边显式做同类型转换，示例关联写法：
  ON unified_temp.mpxn = CAST(health.mpxn AS NVARCHAR(20))
  同时提前过滤health表中不符合“8-13位纯数字”规则的MPXN值，避免脏数据触发转换
• 快速定位隐式转换位置的方法：在报错的查询语句前开启执行计划统计，输入SET STATISTICS PROFILE ON后再执行查询，从输出的执行计划树里直接找到标注为CONVERT(float, ...)的算子，对应就是问题触发点，不需要逐行排查代码

关键提示：当前环境为SQL Server 2008版本，没有TRY_CONVERT的容错转换能力，所有的类型转换都必须在写入临时表之前完成格式规整，不能把格式清洗的逻辑留到关联查询阶段做

内容的提问来源于stack exchange，提问作者r.baton