Beware of Implicit Datatype Conversion in SQL Server

Do you know SQL Server does Implicit Conversion in Queries? If you know that then you are safe. If you don't then please don't miss reading this Complete Article.

Now we'll create a Test Table and Insert Some Values, to understand this concept more clearly.

CREATE TABLE [dbo].[Test3]( [N1] [int] NOT NULL CONSTRAINT [PK_Test3] PRIMARY KEY,
[V1] [nvarchar](10) NULL, )
INSERT INTO Test3 VALUES(1,'1000')
INSERT INTO Test3 VALUES (2,'1001')
INSERT INTO Test3 VALUES (3,'1003')
INSERT INTO Test3 VALUES (4,'1004')
INSERT INTO Test3 VALUES (5,'200')

If we try to do

SELECT * FROM Test3 WHERE V1>1002

Will it Work? It will work. This is where Implicit Conversion is Happening. Whenever SQL Server finds Arithmetic Operator it will convert string to Number if it can do that. This Facility is not Provided in DB2. It will throw exception. But thats a good advantage in DB2. So that people will never get in trap.

Output for Above Query will be

N1	V1
3	1003
4	1004
5	200