奇怪的SQL Server类型转换问题 [英] Strange SQL Server type conversion issue

问题描述

我今天遇到了一个奇怪的问题。我的一个项目是运行.NET + SQL Server 2005 Express。
我使用一个查询进行一些过滤。

I've experienced today strange issue. One of my projects is running .NET + SQL Server 2005 Express. There is one query I use for some filtering.

SELECT *
  FROM [myTable]
  where UI = 2011040773395012950010370
GO

SELECT *
  FROM [myTable]
  where UI = '2011040773395012950010370'
GO

UI列为 nvarchar（256），并且传递给过滤器的UI值始终为25位数字。

UI column is nvarchar(256) and UI value passed to filter is always 25 digits.

在我的DEV环境上-两个查询都返回同一行，并且没有错误。但是，对我的客户而言，经过几个月的良好运行，第一个版本开始返回类型转换错误。

On my DEV environment - both queries return same row and no errors. However at my customers, after few months of running fine, first version started to return type conversion error.

有什么想法吗？

我不是在寻找解决方案-我正在寻找解释为什么在一种环境下它可以工作而在另一种环境下不能工作，为什么突然开始返回错误而不是结果。我在两个服务器上都使用了相同的工具（SQL Server Management Studio Express和2个不同的.NET客户端）

I'm not looking for solution - I'm looking for explanation why on one environment it works and on other doesn't and why out of sudden it started to return errors instead of results. I'm using same tools on both (SQL Server Management Studio Express and 2 different .NET clients)

环境大致相同（W2k3 + SQL Server 2005 Express ）

Environments are more or less the same (W2k3 + SQL Server 2005 Express)

推荐答案

由于数据类型优先级

为此，UI列将更改为十进制（25 ，0）

For this, the UI column will be changed to decimal(25,0)

where UI = 2011040773395012950010370

这几乎是正确的。右边是varchar，并更改为nvarchar

This one is almost correct. The right hand side is varchar and is changed to nvarchar

where UI = '2011040773395012950010370'

这是真正的正确版本，两种类型都相同

This is the really correct version where both types are the same

where UI = N'2011040773395012950010370'

错误之所以会开始，是因为 UI列现在包含一个不能将CAST转换为十进制（25,0）的值。

Errors will have started because the UI column now contains a value that won't CAST to decimal(25,0).

一些不相关的注释：

• 如果您在UI列上有索引，则由于需要隐式CAST，在第一个版本中会将其忽略


• 您需要使用unicode来存储数字吗？有一个严重的开销，其中存储和性能具有Unicode数据类型


• 为什么不使用 char（25）或 nchar（25）是值始终固定长度？您的查询使用存储空间过多，无法达到优化要求根据 nvarchar（256）

• if you have an index on the UI column it would be ignored in the first version because of the implicit CAST required
• do you need unicode to store numeric digits? There is a serious overhead with unicode data types in storage and performance
• why not use char(25) or nchar(25) is values are always fixed length? Your queries use too much memory as the optimiser assumes an average length of 128 characters based on nvarchar(256)

假定平均长度为128个字符编辑，在评论后

Edit, after comment

当您不知道起作用时，不要假定有时为什么起作用

Don't assume "why does it works sometimes" when you don't know that it does work

示例：

• 该值本可以删除然后再添加


• 一个TOP子句或SET ROWCOUNT可能意味着未达到有问题的值


• 该查询从未运行过，因此不会失败


• 该错误是否被其他代码静默忽略？

• The value could have been deleted then added later
• A TOP clause or SET ROWCOUNT could mean the offending value is not reached
• The query was never run so it couldn't fail
• The error is silently ignored by some other code?

编辑2以获得更多清晰度

Edit 2 for hopefully more clarity

聊天

gbn：

当您运行WHERE UI = 2011040773395012950010370时，您不知道行访问的顺序。因此，如果一行确实有自行车，您可能会也可能不会点击该行。

When you run WHERE UI = 2011040773395012950010370, you do not know the order of row access. So if one row does have "bicycle" you may or may not hit that row.

随机：

所以问题可能不在我尝试访问的行中，而是其他值损坏的行？

So the problem may be not in the row which i was trying to access but other one with corrupted value?

gbn

根据服务包级别，索引和表，不同的机器将具有不同的读取顺序碎片，CPU数量，并行度

different machines will have different order of reads based on service pack level, index and table fragmentation, number of CPUs, parallelism maybe

正确

甚至是TOP。这类东西

and TOP even. That kind of stuff

正如Tao提到的，重要的是要了解另一个不相关的东西甚至可以使查询中断

As Tao mentions, it's important to understand that another unrelated can break the query even if this one is OK.

数据类型优先级会导致该列中的所有数据在计算where子句之前都被转换

data type precedence can cause ALL the data in that column to be converted before the where clause is evaluated

这篇关于奇怪的SQL Server类型转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！