SELECT * Not returning all rows, unless I ORDER BY id DESC

Question

Application presented with "Sequence Contains More Than One Entity" error. Knowing this is usually the result of a .SingleOrDefault() command in Linq, I started investigating. I can verify that the production server has many instances of duplicate keywords, so that's where I begin.

I have the following Keyword table:

• id INT (NOT NULL, PRIMARY KEY)
• text NVARCHAR(512)
• active INT

active is just a way to "enable/disable" data if the need strikes me. I'm using LINQ to SQL and have the following method implemented:

public Keyword GetKeyword(String keywordText)
{
    return db.Keywords.SingleOrDefault(k => (k.text.ToUpper() == keywordText.ToUpper()));
}

The idea is that I attach keywords through an association table so that multiple objects can reference the same keyword. There should be no duplicate text entries within the Keyword table. This is not enforced on the database, but rather through code. This might not be best practice, but that is the least of my problems at the moment. So when I create my object, I do this:

Keyword keyword = GetKeyword(keywordText)
if(keyword == null)
{
    keyword = new Keyword();
    keyword.text = keywordText;
    keyword.active = Globals.ACTIVE;
    db.Keywords.InsertOnSubmit(keyword);
}
KeywordReference reference = new KeywordReference();
reference.keywordId = keyword.id;
myObject.KeywordReferences.Add(reference);
db.SubmitChanges();

this code is actually paraphrased, I use a repository pattern, so all relevant code would be much longer. However, I can assure you the code is working as intended, as I've extensively tested it. The problem seems to be happening on the database level.

So I run a few tests and manual queries on my test database and find that the keyword I passed in my tests is not in the database, yet if I do a JOIN, I see that it is. So I dig a little deeper and opt to manually scan through the whole list:

SELECT * FROM Keyword

return 737 results. I decided I didn't want to look through them all, so I ORDER BY text and get 737 results. I look for the word I recently added, and it does not show up in the list.

Confused, I do a lookup for all keywordIds associated with the object I recently worked on and see a few with ids over 1,000 (the id is set to autoincrement by 1). Knowing that I never actually delete a keyword (hence the "Active" column) so all ids from 1 to at least those numbers over 1,000 should all be present, I do another query:

SELECT * FROM Keyword ORDER BY id

returns 737 results, max ID stops at 737. So I try:

SELECT * FROM Keyword ORDER BY id DESC

returns 1308 rows

I've seen disparities like this before if there is no primary key or no unique identifier, but I have confirmed that the id column is, in fact, both. I'm not really sure where to go from here, and I now have the additional problem of about 4,000+ keywords on production that are duplicate, and several more objects that reference different instances of each.

Answer 1

If you somehow can get all your data then your table is fine, but index is corrupted.

Try to rebuild index on your table:

ALTER INDEX ALL ON Your.Table
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

In rare situations you can see such errors if your hard drive fails to write data due to power loss. Chkdsk should help