Strange SQL Server type conversion issue

Question

I've experienced today strange issue. One of my projects is running .NET + SQL Server 2005 Express. There is one query I use for some filtering.

SELECT *
  FROM [myTable]
  where UI = 2011040773395012950010370
GO

SELECT *
  FROM [myTable]
  where UI = '2011040773395012950010370'
GO

UI column is nvarchar(256) and UI value passed to filter is always 25 digits.

On my DEV environment - both queries return same row and no errors. However at my customers, after few months of running fine, first version started to return type conversion error.

Any idea why?

I'm not looking for solution - I'm looking for explanation why on one environment it works and on other doesn't and why out of sudden it started to return errors instead of results. I'm using same tools on both (SQL Server Management Studio Express and 2 different .NET clients)

Environments are more or less the same (W2k3 + SQL Server 2005 Express)

Answer 1

This is completely predictable and expected because of Datatype precedence

For this, the UI column will be changed to decimal(25,0)

where UI = 2011040773395012950010370

This one is almost correct. The right hand side is varchar and is changed to nvarchar

where UI = '2011040773395012950010370'

This is the really correct version where both types are the same

where UI = N'2011040773395012950010370'

Errors will have started because the UI column now contains a value that won't CAST to decimal(25,0).

Some unrelated notes:

• if you have an index on the UI column it would be ignored in the first version because of the implicit CAST required
• do you need unicode to store numeric digits? There is a serious overhead with unicode data types in storage and performance
• why not use char(25) or nchar(25) is values are always fixed length? Your queries use too much memory as the optimiser assumes an average length of 128 characters based on nvarchar(256)

Edit, after comment

Don't assume "why does it works sometimes" when you don't know that it does work

Examples:

• The value could have been deleted then added later
• A TOP clause or SET ROWCOUNT could mean the offending value is not reached
• The query was never run so it couldn't fail
• The error is silently ignored by some other code?

Edit 2 for hopefully more clarity

Chat

gbn:

When you run WHERE UI = 2011040773395012950010370, you do not know the order of row access. So if one row does have "bicycle" you may or may not hit that row.

Random:

So the problem may be not in the row which i was trying to access but other one with corrupted value?

gbn

different machines will have different order of reads based on service pack level, index and table fragmentation, number of CPUs, parallelism maybe

correct

and TOP even. That kind of stuff

As Tao mentions, it's important to understand that another unrelated can break the query even if this one is OK.

data type precedence can cause ALL the data in that column to be converted before the where clause is evaluated