1

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Random
  • 4,519
  • 2
  • 38
  • 46

1 Answers1

5

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

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Yes i know that part but what the question is why it started to happen today? App was working fine with the first version since may and even today hor half a day it was working fine... – Random Aug 01 '11 at 09:26
  • To unrelated notes - i also now these and will pas to product RnD since i dont have a control of DB – Random Aug 01 '11 at 09:26
  • @Random: like I said, you have a value that is no longer decimal(25,0) in the database. It could have been deleted then added again. Or the query was never run so it couldn't fail. Don't assume "why does it works sometimes" when you don't *know* is does work. What if the error is silently ignored by some other code? – gbn Aug 01 '11 at 09:31
  • i've tested in 3 different clients on 2 different machines. On one machines every client is able to run both versions on second only second one works (it started today). I do not control insert and delete in that area and i'm pretty sure none of them happenend between time when it was working and a time when it stops – Random Aug 01 '11 at 09:41
  • it is not about assumptions right now - because i understand underlying nature of the problem but how to explain it to the customer? – Random Aug 01 '11 at 09:42
  • @Random: the explanation is "we have a bug in our code". You compare a nvarchar column which could contain anything to a decimal number. Of course it will fail at some point... – gbn Aug 01 '11 at 09:44
  • "We have a bug in our code" is already there, now the question is - "So why that code passed all tests and few months of PRD runtime" ? – Random Aug 01 '11 at 09:46
  • @Random: Because you didn't have correct test data eg put the word "bicycle" into the UI column. Do you have *exactly the same data* as the customer has *now* to reproduce it? – gbn Aug 01 '11 at 09:49
  • UI column since always had same kind of data of 25 digits starting with 2011 so there was not leading zeros and that particular value is copied from PRD where it fails but on my dev env it works - WHY? – Random Aug 01 '11 at 09:52
  • @gbn let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/2014/discussion-between-random-and-gbn) – Random Aug 01 '11 at 09:52
  • 2
    Good answer, but I would bold/highlight the important/noxious part for someone who doesn't immediately understand how the other unrelated/unselected data in the column causes the query to break: "Errors will have started because the UI column now contains a value that won't CAST to decimal(25,0)" (and maybe a corrolary to reiterate: ", and the data type precedence can cause ALL the data in that column to be converted before the where clause is evaluated") – Tao Aug 01 '11 at 10:44