Index Problem?

If a table has corrupted indexes, can I still query against it in SQL Enterprise?

I have the following statement as the data source of my data list.

SELECT productid, prodname, prodprice from products inner join vendors on products.vendorid = vendors.vendorid where vendorid = 185

The data list takes long time to populate and returns no row. I thought maybe the index of my Products table got corrupted, so I ran the same statement in SQL statement and got 3 rows of data. I am clueless now. Any suggestion?

Thanks

Bugme.Sounds to me the table is locked or at least a few rows.

on the side: I'd expect something like "Ambiguous column name 'vendorid '."|||Can you look at the sticky and post some more info.

Have you tried DBCC CHECKTABLE?

And what do you mean SQL Enterprise?

Space...the final frontier......|||It's the Enterprise Manager.

I ran DBCC CHECKTABLE and got the following --

There are 8832 rows in 82 pages for object 'products'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Since I didn't get any error from DBCC CHECKTABLE, does it mean that the index on products table is fine?

If the table is locked, how can I unlock it?

Thanks

SHK|||The EM might do the locking, I'm not sure how it's used. What happens if you'd exit the EM, run the SQL Analyzer?|||I am not sure I understand where you are going with the question. Theoretically, you can query a table that has corrupt non-clustered indexes, so long as you are not using the corrupt index to access the table. Is your base problem that the query is just slow?|||ProductID is the clustered indexed field and I tried to query against ProductID in EM, I got data.

The problem is when I run the query in my application, it returns no data. When I run the same query in EM, I get data. The problem only happens to vendorid 185. If I run the application with different vendorid, it works just fine. I checked the index on the vendors table too and it's fine.

Weird! Help.

SHK.|||Well, we can rule out miracles. Post the exact query from the .asp page, and the query you run from Query Analyzer. I expect a join has been fouled up, or the where clause in the .asp page is more involved.

Index corrupted

We need to know, how to reorganize or reindex on sql-ce?

With SQL CE 2.0, the only mechanism to re-calculate index stats and repair corruption is to call the Compact method on the SqlCeEngine object. If you want to assess the degree of selectivity of your current index, you can open your SQL CE database in Pocket Query Analyzer and run sp_show_statistics_steps 'table', 'index'

Check the SQL CE Books Online for an example of using the Compact operation.

-Darren