在这种情况下，SQL Server 隐式类型转换如何工作？
当我在 SQL Server 中尝试此即席查询时（假设 UserId 是一个 NVARCHAR 字段）：
SELECT * FROM MyUser WHERE UserId = 123456
我收到此错误：
Msg 245, Level 16, State 1, Line 1 Syntax error converting the nvarchar value 'foo' to a column of data type int.
显然，我的某处有一个值 'foo' UserId 列。
为什么 SQL Server 尝试将我的整个列转换为 INTEGER，而不是执行对我来说显而易见的操作：将我的搜索值转换为 NVARCHAR？
When I try this ad-hoc query in SQL Server (assume UserId is a NVARCHAR field):
SELECT * FROM MyUser WHERE UserId = 123456
I get this error:
Msg 245, Level 16, State 1, Line 1 Syntax error converting the nvarchar value 'foo' to a column of data type int.
Obviously there is a value 'foo' somewhere down my UserId column.
Why is SQL Server trying to convert my entire column to INTEGER instead of doing what seems obvious to me: converting my search value to NVARCHAR?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
比较是使用数据类型优先级的规则完成的：
优先级1表示最高。因此，
NVARCHAR类型（优先级 25）会转换为int（优先级 16），因为NVARCHAR< /code> 在此范围内的优先级较低。The comparison is done using the rules of Data Type Precedence:
Precedence 1 means the highest. So
NVARCHARtype (precedence 25) is converted toint(precedence 16), becauseNVARCHARhas a lower precedence in this scale.我认为这是因为它无法比较不同类型的两个值。然后，必须将两个相等比较成员转换为相同类型。我想，在这里，
int是首选。我相信
int优先于nvarchar类型，因此它必须隐式尝试将nvarchar转换为 int 值。编辑#1
是的，如果可以的话那就太好了。但我想这是因为在尝试其他类型的比较时会尝试暗示太多的转换。
vs
在第一个示例中，用户的意图是什么？是验证
dateOfBirth是否等于1976-06-16的日期值还是与1976 - 6 - 16的整数值进行比较code>，这将导致1954，这足以合理地将其视为任何给定日期的年份。我认为存在从 nvarchar 到 datetime 等隐式转换，但有很多内容需要涵盖，因此他们将自己限制为最常见的可能转换。
I think this is because it cannot compare two values of different types. Then, is has to convert either equality comparsion members to the same type. Here,
intis prefered, I guess.I believe an
inthas a precedence over thenvarchartype, so it must implicitly try to convert thenvarcharto the int value.EDIT #1
Yes, this would be nice if it did. But I guess that is because there would be too much conversion tries to imply when trying some other sort of comparison.
vs
In first example, what is the intention of the user? Is it to verify whether
dateOfBirthis equals to the date value of1976-06-16or to compare with an integer value of1976 - 6 - 16, which would result to1954, which could be reasonable enough to consider it as the year of a any given date.I think there is implicit conversions such
nvarchartodatetime, but there would be much to cover, so they limited themselves to the most common possible conversion.如果您知道列是 NVARCHAR，为什么还要指定整数值？
不幸的是，与许多 SQL 实现一样，SQL Server 在类型支持方面远远落后于其他语言。类型检查通常仅在运行时执行。因此，像您这样的查询不会经过语法检查来突出显示您在这里遇到的问题。您的查询有问题，因为您不匹配不同的类型，但由于 SQL Server 不会对其进行验证，因此结果将是不可预测的，具体取决于数据。
Why are you specifying an integer value if you know the column is NVARCHAR?
Unfortunately SQL Server, like many SQL implementations, is a long way behind other languages in its type support. Type checking is usually only performed at runtime. So queries like yours aren't syntax checked to highlight problems like the one you have here. Your query is at fault because you are mismatching different types, but because SQL Server doesn't vaidate it the results will be unpredictable, depending on the data.