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

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 2

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.