trouble with select with in function on a table

[g_g_99]

 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 ?

[Breck_Carter]

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 🙂

[justin_willey]

Breck's point about being concerned is very true - a validation won't have fixed any problems, in the way that say unloading and re-loading may fix a corrupt index. A similar sounding problem that I had some years ago WAS bad RAM, it does happen. In our case the problem was very high up in the addresses so it only happened very occasionally on that particular machine. I don't know how it works on modern OSs (this was NetWare) but it seemed that other things being equal RAM was allocated from the bottom up.

[g_g_99]

Breck, thanks for the heads up. My PC did have a number of crashes on wake up from hibernation/sleep.

I shutdown the PC, turned off power to it, unseated and re-seated firmly the memory chips (OCX with aluminum fins, not the cheap one but supposedly designed for overclock but I did not).

I went thru memory diagnostic 4 times over, no problem turned up.

Once I turned on the PC I loaded up apps to the brink, 7.5 out 8 GB. Ran Sybase central, open isql, ran the select with in. no problem.

let it the system idled till it went hibernate at night. next afternoon I woke up the machine uneventfully. every app still functioned without hiccup including VirtualBox

Hopefully it was a loose memory chip connection that got fixed. If I can run the PC w/o further crashes up for a couple weeks, I would blame problems on the loose connection and can feel at ease using the PC a couple years more. no more creepiness :):)

[MarkCulp]

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.

[g_g_99]

the key is the primary key for the table.

[g_g_99]

thanks, despite full validation was completed without any complain, the select with in works now

good thing the database was relatively small and validation ran through quickly in matter of minute or two.