October 25, 2011 at 2:06 am
We have a third party SQL 2000 Database which is suddenly giving strange results in that a simple equal is not returning records but when we use like it does return records but only in some cases.
select * from pa_payts where pa_policy = '51022059' not returning records
select * from pa_payts where pa_policy like '51022059%' is returning records
The table has 28 million records and pa_policy is char(8). It looks like around 16,000 records maybe at issue. The database itself is 42GB.
The table was recreated by the third party developers 4 months ago. There are several indexes including an index on pa_policy.
Any advice on what should be done would be appreciated. My first thoughts are drop and recreate the indexes but we can not touch production at the moment and are waiting for a backup to be copied to test area.
October 25, 2011 at 4:54 am
It's highly unlikely that an index would change the results of a query. It looks like you have an extra space at the end of the line, which is why the equals is not working.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 25, 2011 at 4:59 am
Grant Fritchey (10/25/2011)
It's highly unlikely that an index would change the results of a query. It looks like you have an extra space at the end of the line, which is why the equals is not working.
Extra space was my first thought but the field in Char(8) and the policy number is 8 characters.
October 25, 2011 at 5:14 am
You could add a SELECT LEN() just to verify that.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 25, 2011 at 5:21 am
If 51022059 genuinely is one of the policy numbers with which you have this problem, run
select len(pa_policy), pa_policy from pa_payts where pa_policy like '51022059%' and pa_policy <> '51022059'
That will at least tell you whether there are somehow more than 8 characters there. Yes, it's a char(8) field - but that means your problem can't be happening, and it is; so something impossible is going on, so check whether an impossible length problem has crept in - and if it has, you know what you need to fix.
Another thing to try could be
select * from pa_payts where pa_policy <= '51022059'
and see if that produces the same result as = or the same result as like.
Tom
October 25, 2011 at 5:41 am
Also worth running this:
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2011 at 5:41 am
I know that this is a long shot, but I’ve had a case once that an index was corrupted, so when it was used in a query plan, we did not get the results. When we didn’t use the index, we got the expected results. Rebuilding the index solved the problem (and of course we ran dbcc checkdb on the whole DB). If the 2 queries use different query plans and the one without the like is using an index that is not used in the query that uses the like operator, you can try and run dbcc check on the table and its indexes (if you find a problem, don’t forget to run the check on all the database that are using the same disk)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 25, 2011 at 8:21 am
Looks like one of the six indexes was partially corrupted when we deleted the index the query worked correctly and then still worked after recreating the index.
Thanks for everybodys input
October 25, 2011 at 1:36 pm
Don't forget to run DBCC CHECKDB to check if you have other problems in the database. I would do it for all databases that are using the same disk/storage.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply