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?
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.
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.