January 19, 2012 at 4:29 am
Hi,
Can anyone tell me what is the exact behaviour or difference between the below two sql statements and why i am getting the error.
I beleive 1 is exact match and other it is comparing all the values in the column.
Just wanted to understand how sql internally it is treating both the sql stmt's in a different manner.
create table test
(id varchar(1)
)
insert into test values('*')
insert into test values('/')
insert into test values('1')
insert into test values('2')
select * from test where id ='1'
select * from test where id=1
Thanks in Advance.
January 19, 2012 at 4:40 am
You are getting an error because you are forcing the server to do an implicit conversion of data type. The column’s data type is varchar(1). In the second select query you are comparing the column to 1 which is an integer data type. Whenever SQL Server is using an implicit conversion, it uses a table which tells it the precedence of each data type (you can find out about the precedence in http://msdn.microsoft.com/en-us/library/ms190309.aspx%5B/url%5D. Since int data type takes precedence over varchar data type, it tries to convert the values in the table into int and it failes.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply