auto conversion in sql 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

auto conversion in sql 2005

Author

Topic

hamid.y
Starting Member

22 Posts

Posted - 2010-02-26 : 03:42:14

is it true when you compare tow value with different data type like '123'=123 and the answer is true and no problem happened?
whats the reason?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-26 : 03:45:33

It is known as implicit conversion.
For example
select '666' + 1 results in 667 as integer

No, you're never too old to Yak'n'Roll if you're too young to die.

hamid.y
Starting Member

22 Posts

Posted - 2010-02-26 : 03:49:44

thanks for your answer.
and it it the same when comparing to field with data type char and vharchar or nvarchar?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-26 : 03:57:57

quote:
Originally posted by hamid.y

thanks for your answer.
and it it the same when comparing to field with data type char and vharchar or nvarchar?

Yes.. Implicit conversion happens for char , varchar & nVarchar.

Below is example for your understanding..

--1st Example with Variables

Declare @TestV varchar(50)
Declare @TestN nvarchar(50)
Declare @TestC char(50)

Set @TestV ='MyTest'
Set @TestN ='MyTest'
Set @TestC ='MyTest'

If @TestV = @TestN
Print 'Same'
Else
Print 'Different'

If @TestV = @TestC
Print 'Same'
Else
Print 'Different'

--2nd example with table

Declare @NewTable Table
(Srno Int identity,
Testv varchar(10),
TestC Char(10),
TestN Nvarchar(20))

Insert into @NewTable
Values ('Test1','Test1','Test1')

--It will select records where both char and varchar value is same
Select 'Varchar N Char Comp' , * from @NewTable Where Testv = TestC

--It will select records where both char and nvarchar value is same
Select 'Varchar N NVarchar Comp' , * from @NewTable Where Testv = TestN

Regards,
Bohra

hamid.y
Starting Member

22 Posts

Posted - 2010-02-26 : 04:03:04

thanks millions.
just as last question.
did implicit conversion exist in the sql 2000 and what differences has happened in sql server 2008 for implicit conversion in comparison with 2005?

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 04:07:46

I think it is better to use Explicit conversion


SELECT ...
FROM MyTable
     WHERE VarcharColumn = 123

will work if VarcharColumn contains numbers. But you could write this as


SELECT ...
FROM MyTable
     WHERE CONVERT(int, VarcharColumn) = 123


SELECT ...
FROM MyTable
     WHERE VarcharColumn = CONVERT(varchar(10), 123)

They behave differently. The first one will fail if MyTable has a row where VarcharColumn does not contain a valid number (maybe it contains only valid numeric digits at present, perhaps someone will add a non-numeric value in the future?? then your program will break). The second one will work whatever VarcharColumn contains.

Also, I think explicit conversion is clearer to anyone else who has to maintain your program.

And finally, when SQL 2000 SP4 came out there were changes to query plans which made a huge (as in they took MUCH longer) difference to query plans where IMplicit conversion had been used / relied on.

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 04:08:53

"did implicit conversion exist in the sql 2000 and what differences has happened in sql server 2008 for implicit conversion in comparison with 2005?"

Implicit conversion has always existed. There is the possibility that Query plan will be very different in SQL 2000 and SQL 2005

hamid.y
Starting Member

22 Posts

Posted - 2010-02-26 : 04:16:13

thanks alot for your useful info.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources