September 10, 2012 at 9:47 am
Sean Lange (9/10/2012)
hehe I hadn't looked at today's question of the day. Seems to be in conjunction with this post. Same issue exactly. We should all three get this one correct with no problem. 😉
Shocking how many thought it would return a valid answer, isn't. Granted, 24% isn't a lot, but still.
September 10, 2012 at 3:18 pm
Andrew-495157 (9/10/2012)
Lynn wrote "You really don't get it, do you"In the UK this would be considered a provocative and rude statement. I will assume cultural differences apply here.
Having used RBDMS since 1984, and having an MSc in Computing, I am prepared to assert that "actually,yes, I do get it".
Lynn wrote "trying to blame SQL for the failure of the author"
Again provocative - and again I have to assume a cultural difference here.
In the professional world "Blame" is a useless concept. Finding out what has happened, understanding it, and taking steps to prevent re-occurence are useful.
==========================================================================================================
The error occurred in a very old system, which itself had been migrated from a legacy RDBMS. In the original system the validation of data entry meant that the nVarchar column could only possibly have Int (as strings) values. The system had been enhanced and at some point the business model needed to allow non-numeric text to be stored in the column using another data-entry system. There is almost no documentation of the system.
As usual a series of errors going back a long way lead to the defect showing up after years of untroubled use. I don't think anyone now knows the "who did what, when, or why". The important point is that we all learnt things - about the business domain, actual use of the system, SQL etc.
SQL Server is itself, of course, a very old system extended from legacy systems over decades, and does not fully implement the RDB model (as Lynn correctly points out neither does Oracle. Nor in fact does any mainstream commercial RDBMS). SQL is riddled with inconsistencies and quirks, as is SQL server. The history of SQL and the development of RDBMS since 1971 is very interesting - both technically and commercially. It is however a deeply flawed product. It is interesting to see Microsoft gradually re-engineering SQL (essentially towards .Net) to make it a more modern and less flawed product.
======================================================================================================
I don't intend to post any more on this topic myself as I believe we now fully understand the issues and know what steps to take.
Thanks for all who contributed. Live long and prosper
OP, I think the people who have been posting in this thread (who, by the way, are some of the most knowledgeable and helpful SQL Server experts out there) are feeling some frustration because you keep saying that SQL Server somehow "failed" in this instance. The fact of the matter is that SQL Server applies very well-documented datatype precedence rules when attempting to compare values of different datatypes. At least two posters provided links to that information, but in short, when attempting to compare a varchar() value to an int value, SQL Server will ALWAYS attempt to convert the varchar() value to int - int has the higher datatype precedence. The fact that you think SQL Server SHOULD compare 'AB' = 1609 as if it were 'AB' = '1609' is really beside the point - that's NOT how it works, and the actual behavior is very well-defined and predictable.
The second point of contention seems to be your insistence that SQL Server is somehow inconsistent or flawed because your stored procedure worked fine with the value 'AB' in the table. T-SQL is a declarative language - you tell the RDBMS *what* you want and it determines *how* it will get that result. Even a query as simple as the snippet you posted could be satisfied through a number of different execution plans, and which plan gets used depends on criteria like the distribution of values in the data, the number of rows involved, and the availability of indexes, most of which are unique to a specific schema and dataset. It is entirely possible that SQL Server executes your snippet with a plan that does not require it to evaluate the row with the 'AB' value for some sets of parameter/variable values, thus throwing no error. Understanding how SQL Server evaluates and executes queries requires a deep understanding of the internal workings of the database engine.
Finally, I think some people may be offended by the attitude implied by your posts. Most of us on these forums work with SQL Server all day every day and appreciate its capabilities, understand its limitations, and collectively, possess a vast amount of knowledge of and experience with SQL Server. In this specific instance, several posters immediately pinpointed the source of your problem as a simple datatype precedence issue that could be solved by harmonizing the datatypes of your parameters/variables with the datatypes of the columns in your tables. Rather than thanking them for the accurate diagnosis of the error and straightforward suggestions for resolving it, you brushed them aside to insist that SQL Server is somehow "wrong" in how it handles your query and insinuate that you, with your MSc in computer science and all, know better. May I suggest that you approach your colleagues in these forums with some humility and collegiality rather than the arrogance and pedantry I read into your posts in this thread?
Jason Wolfkill
November 27, 2012 at 4:40 pm
Stumbled upon this thread when trying to find cause of a similar issue that we experienced recently.
And our conclusion match the OPs as far as the SQL query plans. In our case the cause of the problem was a nvarchar column being used as a datetime in a parameterized query. The table has 4 million rows.
The column always has had non-date string values, such as mm/dd/yyyy, but it was only last week that the conversion started to fail. Now, sometimes it works and sometimes it doesn't. I used the SQL profiler to capture the query in question and ran it in SSMS and it worked fine. Then I removed a space in the parametized query's string and the query failed. Put the space back in and it worked again. Kept going back and forth and the same thing happened.
Later in the day the query stopped working completely but the webpage generating this same query still worked. Our best guess is that it has something to do with SQL Server caching the query execution plans.
It's hard to believe this issue, until you have experienced it yourself. Of course the code has been fixed now, but the underlying inconsistency of SQL server was driving me nuts.
Viewing 3 posts - 46 through 47 (of 47 total)
You must be logged in to reply to this topic. Login to reply