select * from dba.aTbl where key in (1,2,9,10)
gave me only 2 records but if I change the criteria to
key=1 or key=2 or key=9 or key=10
then I got all 4 records, I have recently updated the database in sql 11.01. to the latest version so I could try out clr integration what can I do to fix the problem?
must the table have index built for primary key in order to use in( ) function ?
That is a very creepy symptom... IN doesn't work, then works again after dbvalid.
You may be having a problem with bad RAM. That can masquerade (actually pretty much always DOES masquerade) as other kinds of problems like corrupt disk data. In your case, the "corruption went away" which could mean the RAM problem is transient, or the data was placed in a different (good) part of RAM in the test that worked.
If you suspect bad RAM, stop using that computer for anything important right away... data temporarily corrupted by bad RAM can easily be written back to disk making the corruption permanent.
Do a google search on "memtest" and pick one to run. Wiggling the RAM to reseat it often works. If you bought bargain-priced RAM then you got what you paid for (e.g., you let Best Buy pick the brand when upgrading :)
An index is not required - without an index on key the query engine will choose a table scan and should find all of the rows. Is 'key' the primary key for the table? If yes then there is already an index built for key.
The best explanation is that you have a corrupted database. Try validating your database (i.e. run dbvalid). If the problem is a bad database then you will need to rebuild (unload/reload) the database.
answered 18 Jul '13, 20:54