Posted by joshsackett on 11/16/05 00:05

I am having a problem with indexes on specific tables. For some reason
a query that runs against a view is not selecting the correct index on
a table. I run the same query against the table directly and it looks
fine. Can anyone give me some insight? Thanks.

PRODUCTION1:
CREATE TABLE MyTest1 (ID INT IDENTITY(1,1), COLUMN1 CHAR(10), COLUMN2
CHAR(10))
CREATE CLUSTERED INDEX IDX_MyTest1 ON MyTest1 ON (ID)
CREATE NONCLUSTERED INDEX IDX_MyTest2 ON MyTest1 ON (COLUMN2, ID)

ARCHIVE1:
CREATE TABLE MyTest1 (ID INT IDENTITY(1,1), COLUMN1 CHAR(10), COLUMN2
CHAR(10))
CREATE CLUSTERED INDEX IDX_MyTest1 ON MyTest1 ON (ID)
CREATE NONCLUSTERED INDEX IDX_MyTest2 ON MyTest1 ON (COLUMN2, ID)

REPORTDB:
CREATE VIEW MyTest1 AS
SELECT ID, COLUMN1, COLUMN2 FROM PRODUCTION1..MyTest1
UNION ALL
SELECT ID, COLUMN1, COLUMN2 FROM ARCHIVE1..MyTest1

While in PRODUCTION1:
SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
--> Clustered index seek PRODUCTION1..IDX_MyTest2
--> Results returned

While in ARCHIVE1:
SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
--> Clustered index seek ARCHIVE1..IDX_MyTest2
--> Results returned

While in REPORTDB:
SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'
--> Index seek PRODUCTION1..IDX_MyTest2
--> Bookmark lookup PRODUCTION1..IDX_MyTest1
--> Index seek ARCHIVE1..IDX_MyTest2
--> Bookmark lookup ARCHIVE1..IDX_MyTest1
--> Concatenate data and results returned

• Next in forum: Re: Options to upgrade an SQL 6.5 DB to 2000
• Prev in forum: Re: QUESTION: Connecting with SQL Admin across domains
• Thread view: Indexes being improperly used when selecting data through a view

[Reply to this message]