SQL Server where class comparisons with different types and default behavior

Question

That's right ... this question has puzzled me a long time ago, so perhaps one of you bright SQL Server sparks can shed light on this behavior.

We have a Phones table. In it, phone numbers are stored as nvarchars and contain numbers in the international format, only in digital format ... therefore, the number in the USA [phone] stored as 12129999999

For reasons that are beyond the scope, someone wrote me SPROC, which took the phone number as bigint, did not cast, made simple, where clause = comparison, and this worked perfectly fine until some unwanted data got into the column nvarchar to the table that caused it to break. Consider the following test script.

 IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Phones') BEGIN DROP TABLE Phones END GO CREATE TABLE [dbo].[Phones] ( [ID] [int] IDENTITY(1,1) NOT NULL, [Mobile] [nvarchar](50) NOT NULL, CONSTRAINT [PK_Phones] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] GO DECLARE @biMobile_1 bigint DECLARE @biMobile_2 bigint SET @biMobile_1 = 12121111111 SET @biMobile_2 = 12129999999 Print 'Inserting Phone Number' INSERT INTO Phones (Mobile) VALUES ('12121111111') Print 'Selecting Phone Number' SELECT * FROM Phones WHERE Mobile = @biMobile_1 --Select #1 Print 'Inserting Junk Data' INSERT INTO Phones (Mobile) VALUES ('JUNK DATA') INSERT INTO Phones (Mobile) VALUES ('12129999999') Print 'Selecting From Table Containing Junk' SELECT * FROM Phones WHERE Mobile = @biMobile_1 -- Select #2 SELECT * FROM Phones WHERE Mobile = @biMobile_2 -- Select #3 

The first choice (marked # 1) will work. The second choice (marked # 2) will work, but will give an error immediately after the third option (marked # 3) does not return anything.

Error returned:

 Error converting data type nvarchar to bigint. 

Now this is similar to the behavior of bonkers. What I thought would be

• SQL implements a comparison of two different data types in a WHERE
• It will try to convert @variable to column data type
• If it fails, throw an error, it will work, BIG !!!

What really happens is

• SQL implements a comparison of two different data types in a WHERE
• In row by row, it converts the value in the column to the @variable data type
• For each successful conversion, it performs a comparison and returns this string.
• If it falls into a value in a column that it cannot convert, it bombes, returns all the data that it has found so far, and does not continue through the table.

Can someone clarify what is behind this logic, and if there is any special order of priority that SQL Server provides to data types when it decides what to compare / distinguish

Note. I tested this test in SQL 2005, but it also reproduces the behavior in SQL2K.

Answer 1

The data type is correctly selected - http://msdn.microsoft.com/en-us/library/ms190309.aspx

Edit - to clarify, this does not mean that sql always converts the column type to the parameter type. It just follows the priority type in the link I gave. This may mean that the parameter is converted to a column type if it prescribes the type.

Answer 2

I do not understand what the problem is. Why did the SQL server know that record # 232 of # 1000 would bomb? This does not happen until it reaches this entry.

In the meantime, it passes the results back to the client as they are created. This will help increase productivity.

What else do you expect?

Answer 3

Personally, instead of converting the parameter to the correct type, I would declare it as this type to start with.

Answer 4

You describe what is going on perfectly, so I don’t understand what you would like to know that you don’t know yet?

Correcting the request is simple enough, just ask Sql Server to pass your parameter to varchar, instead of distinguishing the varchar column to bigint:

 SELECT * FROM Phones WHERE Mobile = cast(@biMobile_1 as varchar(50)) 

Answer 5

Your logical description of the process is really correct.

SQL Server implies using the Cast / Convert function in a mobile column (in this case, an implicit conversion from nvarchar to bigint). Functions, of course, apply to sequences along the line and, therefore, to the behavior that you see, as a result of which the select statement does not explode until the translation function works.

The problem can be fixed by converting the single numeric variable that is being searched (@ biMobile_2) to nvarchar, instead of having to implicitly have the Mobile column converted for all rows.

For instance:

 SELECT * FROM Phones WHERE Mobile = convert(nvarchar,@biMobile_2) 

Hope this helps.

Answer 6

The problem is that the implicit conversion from nvarchar to bigint is invalid when the contents of nvarchar contains non-numeric characters. The database engine will not execute the query once to check each value that will be returned to check if the query you sent is valid for each row, only to then expand and run the query again to return the results.

Query execution and data return begin until a comparison is found that is invalid.