隐式转换如何与 SQL Server 中整数的 varchar 表示的比较 (< >) 一起工作? [英] How does implicit conversion work with comparisons (< >) of varchar representations of integers in SQL Server?
问题描述
我有一个查询，我将值与 < 和 > 进行比较.数据库很旧而且设计很差，所以不幸的是我们有列应该是 INT 而是 VARCHAR.
I've got a query where I'm comparing values with < and >. The database is old and was poorly designed so unfortunately we have columns that should be INT but instead are VARCHAR.
该列名为 PayCode，类型为 VARCHAR(50).
The column is called PayCode and is of type VARCHAR(50).
如果我有这样的 WHERE 子句:
If I have a WHERE clause like this:
WHERE PayCode > 200
然后，当我查看执行计划时，会出现有关隐式转换和基数估计的警告.
then when I look at the execution plan there are warnings about implicit conversion and cardinality estimates.
我不明白的是为什么下面要去掉这些警告:
What I don't understand is why the below gets rid of those warnings:
WHERE PayCode > '200'
我知道我已经将它们都设为 VARCHAR，但逻辑实际上似乎有效(它不是按字母顺序进行比较)，这意味着它无论如何都将两者都转换为整数.这不是隐式转换吗?
I understand that I've made them both VARCHAR, but the logic actually seems to work (it's not doing an alphabetical comparison) which would imply that it's converting both to integers anyway. Would that not be an implicit conversion?
奇怪的是，以下仍然会导致基数估计警告:
Oddly, the below still causes cardinality estimate warnings:
WHERE CAST(PayCode AS INT) > 200
那么为什么我可以使用大于/小于整数的 VARCHAR 表示，并且既能正常工作又没有隐式转换或基数问题?
So how come I can use greater than/less than with VARCHAR representations of integers and have both the logic work and no implicit conversion or cardinality issues?
推荐答案
那么为什么我可以使用大于/小于整数的 VARCHAR 表示，并且两个逻辑都可以工作.
So how come I can use greater than/less than with VARCHAR representations of integers and have both the logic work.
你不能.<代码>3 <200, 但 '3' >'200'.在对 VARCHAR 进行操作时，比较运算符使用由 COLLATION 定义的字母数字排序顺序.
You can't. 3 < 200, but '3' > '200'. When operating on VARCHAR the comparison operators use an alpha-numeric sort order defined by the COLLATION.
EG:
drop table if exists #t
create table #t(PayCode varchar(20))
insert into #t(PayCode) values ('3')
select *
from #t
WHERE PayCode > '200'
这篇关于隐式转换如何与 SQL Server 中整数的 varchar 表示的比较 (< >) 一起工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
