无法将值转换为浮点数 [英] unable to cast value as float

问题描述

我们有一个 SQL，它在 ColumnA 上执行 CAST 函数(到 FLOAT).SQL 有一个过滤器，它最终会间接过滤掉那些在 ColumnA 中具有非数字值的行.但是，由于我认为是由于并行运行 SQL 的一部分，我相信 CAST 甚至应用于被过滤掉的行，这会导致 SQL 在无法将值转换为浮点数..."

我知道如果我通过添加查询提示来运行一个 proc

选项 (MAXDOP 1)

SQL 按预期运行.我怀疑在 1 proc 上运行会强制应用过滤器来清除 columnA 中具有非数值的行，以便其值的 CASTING 成功.我还发现使用查询提示

选项(强制命令)

解决了这个问题，我假设因为这也确保首先应用过滤器，并且我获得了比在一个圆柱体上运行的查询性能更好的查询性能.

我倾向于使用第二个选项解决问题.如果我对这里发生的事情有任何误解，或者如果有人想阐述我的一般理解或提出建议，我将不胜感激.

我正在运行

<块引用>

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1720.0 (X64) 2010 年 6 月 12 日01:34:59 版权所有 (c) 微软公司企业版(64 位)在 Windows NT 5.2(内部版本 3790:Service Pack 2)上

事后的想法:

T-SQL 有以下函数来检查字符串是否可以转换为特定数据类型，这似乎会很好.

IsFloat是数字的整数等

我真的很恼火，我在我们的数据库中找到了多少列定义为 varchar(255) 的各种数据.我想解决办法是不要那样做！"

解决方案

关于你的想法.

<块引用>

如果 T-SQL 具有以下功能，这似乎会很好检查字符串是否可以转换为特定数据类型.

SQL Server 2012 确实引入了 TRY_CONVERT 为这个需要.因此，以下查询将返回 NULL 而不是错误.

SELECT TRY_CONVERT ( FLOAT, 'Fish')

即使是串行计划，也不能保证 WHERE 子句会在 SELECT 被评估之前发生.正如所解释的 在这篇博文中 从 SQL Server 2005 开始，这种情况比以前的版本更有可能发生.SQL Server 中数据库引擎功能的行为变化2005 特地将其描述如下.

<块引用>

SQL Server 2005 有时会比查询中的表达式更快地求值在 SQL Server 2000 中评估它们时.此行为提供了以下重要好处:

• 能够将计算列上的索引与查询中与计算列表达式相同的表达式进行匹配.
• 防止表达式结果的冗余计算.

有关此行为的更多讨论在 Craig Freedman 的另一篇优秀博客文章中转换和算术错误.

在 2012 和 TRY_CONVERT 之前的版本中，您需要将 CAST AS FLOAT 包装在 CASE 语句中.例如

 SELECT CASE WHEN ISNUMERIC(Col)=1 THEN CAST(Col AS FLOAT) END AS Col发件人表其中 ISNUMERIC(Col)=1

这仍然不能绝对保证防止您收到错误，因为 ISNUMERIC 本身只是检查该值是否会转换为数字数据类型之一，而不是专门用于浮动 输入失败的一个例子是'.'

CASE 记录在网上的书籍中大多是短路(此处讨论了一些例外)

您还可以在连接项 SQL Server 不应引发不合逻辑的错误 和 SQLKiwi 对类似问题的良好解释

We have a SQL that performs a CAST function (to FLOAT) on ColumnA. The SQL has a filter which in the end will indirectly filter out those rows that have non numeric values in ColumnA. However, because of what I believe is due to running portions of the SQL in paralell, I believe that the CAST is even applied to rows that are filtered out and this causes the SQL to fail on "unable to cast value as float..."

I know that if I run with one proc by adding the query hint

OPTION (MAXDOP 1)

that the SQL runs as expected. I suspect that running on 1 proc forces the filter to be applied to weed out the row with the non numeric values in columnA so that the CASTING of its values succeeds. I also found that using the query hint

OPTION (FORCE ORDER)

fixes the issue, I am assuming becausethis too ensures that the filter is applied first and I get much better query performance that one running on one cylinder.

I am leaning torwards fixing the issue using the 2nd option. If I have any misconceptions about what is going on here or if someone would like to expound on my general understand or make a recommendation, I would appreciate it.

I am running on

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1720.0 (X64) Jun 12 2010 01:34:59 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

An afterthought:

It seems that it would be nice is T-SQL had the following functions to check to see if a string could be converted to a particular datatype.

IsFloat IsNumeric IsInteger etc

I really am annoyed at how many columns of all sorts of data that I find in our database that are defined as varchar(255). I guess the solution is "not to do that!"

解决方案

Regarding your after thought.

It seems that it would be nice is T-SQL had the following functions to check to see if a string could be converted to a particular datatype.

SQL Server 2012 does introduce TRY_CONVERT for this need. So the following query would return NULL rather than an error.

SELECT TRY_CONVERT ( FLOAT, 'Fish')

There is no guarantee even with serial plans that the WHERE clause will happen before the SELECT is evaluated. As explained in this blog post from SQL Server 2005 onwards this is more likely to happen than in previous versions. The Behavior Changes to Database Engine Features in SQL Server 2005 specifically calls this out as follows.

SQL Server 2005 sometimes evaluates expressions in queries sooner than when they are evaluated in SQL Server 2000. This behavior provides the following important benefits:

• The ability to match indexes on computed columns to expressions in a query that are the same as the computed column expression.
• The prevention of redundant computation of expression results.

More discussion about this behaviour is in another good blog post by Craig Freedman Conversion and Arithmetic Errors.

On versions prior to 2012 and TRY_CONVERT you need to wrap the CAST AS FLOAT in a CASE statement. e.g.

  SELECT CASE WHEN ISNUMERIC(Col)=1 THEN CAST(Col AS FLOAT) END AS Col
  FROM Table
  WHERE ISNUMERIC(Col)=1

This still isn't absolutely guaranteed to prevent you getting errors as ISNUMERIC itself just checks that the value would cast to one of the numeric datatypes rather than specifically to float An example of an input that would fail is '.'

CASE is documented to mostly short circuit in books online (some exceptions are discussed here)

You can also find additional discussion/complaints about this in the connect item SQL Server should not raise illogical errors and a good explanation of a similar issue by SQLKiwi

这篇关于无法将值转换为浮点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！