Implicit Conversions warning

本文介绍了在SQL Server中如何通过避免隐式类型转换来优化查询性能,防止可用索引未被使用的问题,并通过实例演示了如何在不同情况下展示查询计划警告。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

After adding an index,  ensuring that an index is actually used is probably the biggest win you will get in terms of performance in SqlServer.  There are many ways that an query can be non-sargable, and therefore not using an available index,  a common one is implicit (or even explicit) type conversion.

Within Denali ( or now SqlServer 2012 to give it its correct name) two new warnings have been added to the execution plan to help highlight this issue.  “Type conversion in expressionColumnExpression may affect "CardinalityEstimate" in query plan choice” and “Type conversion in expression ColumnExpression may affect "SeekPlan" in query plan choice” are now shown if a type conversion has happened and it is affecting , or could be , affecting performance.

To demonstrate both I will need to populate a small table

drop table nums
go
create table nums
(
IntCol Char(10)
)
go

insert into nums(IntCol)
Select top(10) row_number() over(order by (select null)) from sys.columns


Notice that the IntCol column is defined as a char(10) type.  If i now execute the following query
 
select * from nums where IntCol = 8
 
In the execution plan viewer, I will see this
image
 
There is a Yellow Exclamation Mark warning on the query it self and the warning is that the type conversion may affect CardinalityEstimate.  Its pretty obvious now what our potential problem is.
 
Additionally, if an index is created:
 
create index Idx1 on nums(IntCol)

and the Select statement re-executed,  this is shown..
 
  image
Even better , In layman's terms “You have an index, but i cant use it”.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值