Recently in one of the forums a question was posted where the developer had run a delete statement similar to the one below
delete from #tmp
where data =1
This deleted all the data in the table, obviously there was a large number of different values for the column data. Now the first and most obvious thought we had was that only the first line was selected and run but actually that wasn’t the case.
A common thing overlooked by developers when writing code is the datatype. In this case the data column was varchar. So what comes after 1 in varchar?
11,12,13,2,111,111 and pretty much everything else.
The below code is a quick demonstration.
create table #tmp
( id int,
data varchar(10)
)
insert into #tmp
select 1 ,'01'
union
select 2 ,'1'
Go
delete from #tmp
where data =1
--notice how two rows got deleted the column value get converted into int
-- confirm this by looking at the execution plan
drop table #tmp
Keep in mind that implicit conversions occur when performing lookups, this often adds to query performance issues and as you can see in this case it can sometime cause data issues as well. Keep in mind that when performing implicit conversion SQL converts the target column into the data type used by the user and not the other way around.
Please Consider Subscribing
