SQL Server, where clauses comparisons with different types & default casting behaviour

Question

Right... this one had me baffled for a while today so maybe one of you SQL Server bright sparks can shed some light on this behaviour.

We have a table Phones. In it, the phone numbers are stored as nvarchars and it contains numbers in International format, in only numeric format... so a US number +1-(212)-999-9999 is stored as 12129999999

For reasons that are beyond, me someone had written a SPROC, that took the phone number as a bigint, did no casting, did a simple where clause = comparison, and this worked absolutely fine, until some junk data got into the nvarchar column on the table which 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 select (marked #1) will work The second select (marked #2) will work but will give an error immediately after The third select (marked #3) returns nothing.

The error returned is

Error converting data type nvarchar to bigint.

Now this seems completely bonkers behaviour. What I thought would happen is

1. SQL realises its comparing 2 different data types in the WHERE clause
2. It would attempt to convert the @variable to the datatype of the column
3. If it fails, throw an error, it it works, GREAT!!!

What actually seems to be happening is

1. SQL realises its comparing 2 different data types in the WHERE clause
2. On a row by row basis, it converts the value in the column to the datatype of the @variable
3. For each successful conversion, it does the comparison, and will return that row.
4. If it hits a value in the column, that it can't convert, it bombs, returns whatever data it has found so far, and doesn't continue on through the table.

Can anyone clarify what the reasoning is behind this logic, and if theres any particular order of precedence that SQL Server gives to Data Types when it's deciding what to compare/cast

Note. I did this test in SQL 2005 but it's replicable behaviour in SQL2K also.

Answer 1

Data type precedence is well defined - http://msdn.microsoft.com/en-us/library/ms190309.aspx

Edit - to clarify, it is not that sql always converts the column type to the param type. It just follows the type precedence in the link I gave. This could mean the param gets converted to the column type, if the type precedence dictates so.

Answer 2

You describe what's happening perfectly, so I don't understand what you'd like to know that you don't know yet?

Fixing the query is easy enough, just ask Sql Server to cast your parameter to varchar, instead of casting the varchar column to bigint:

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

Answer 3

I don't see what the problem is. Why would SQL server know that record #232 out of #1000 would bomb? It doesn't until it gets to that record.

In the meantime, it's streaming the results back to the client as they are generated. This is to help with performance.

What else would you expect?

Answer 4

Hi,

Your logic process description is indeed correct.

SQL Server is implicity applying a Cast/Convert function to the mobile column (in this case an implicit conversion from nvarchar to bigint). Functions are of course applied on a row by row basis and hence the behaviour your are seeing, whereby the select statement does not bomb out until the cast function fails.

The issue can be avoided by converting the single numeric variable that is being searched for (@biMobile_2) to an nvarchar, rather than implicitly having the Mobile column converted for all rows.

For example:

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

Hope this helps.

Answer 5

The problem is that implicit conversion from nvarchar to bigint is only invalid when the contents of the nvarchar contain non-numeric characters. The database engine is not going to execute the query once just to check every value that would be returned to see if the query you submitted is valid for every row, only to then turn around and execute the query again to return the results.

Query execution and returning data commences until it does find a comparison that proves to be invalid.

Answer 6

Personally rather than converting the parameter to the correct type, I would declare it as that type to begin with.