Sql-server – Does the compatibility level for sql server work reliably for scripts

In theory it should work. You better test it thoroughly before converting production. If your application uses DTS packages, they will have to be converted to SSIS. Your OS will have changed as well, so double check to make sure your app does not rely on operating system level functionality.

To further answer your question, the vendor is likely not wanting to support it as a way of covering their butts. They certified the application to run on SQL 2000, not SQL 2008.

I would stop giving them money if I were you.

There are only two possible ways that the execution plan for your query might not read from table1 directly. I will use the following indexed view created in the AdventureWorks sample database to illustrate:

CREATE VIEW dbo.IV
WITH SCHEMABINDING
AS
SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
GROUP BY
    p.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.IV (ProductID);

1. Table1 is an indexed view

In this case, the optimizer will make an estimated-cost-based decision whether to read from the indexed view or to expand the view and read from the base tables it references. Example:

SELECT
    ProductID,
    cnt
FROM dbo.IV;

The optimizer chooses to read from the indexed view directly:

If the optimizer chose instead to expand the view, we could use the NOEXPAND table hint to prevent that. This hint is required in non-Enterprise SKUs to access indexed views directly.

The EXPAND VIEWS query hint forces expansion of the view, leading to a plan that reads from the base tables:

SELECT
    ProductID,
    cnt
FROM dbo.IV
OPTION (EXPAND VIEWS);

2. An indexed view exists which matches the query

SQL Server Enterprise Edition contains a feature which can match queries to indexed views, where the indexed view is not referenced in the query:

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

Despite not mentioning our indexed view, the execution plan does:

About indexed view matching

This matching is only possible where the query processor has guarantees that the rewrite will always produce correct results. These guarantees include the fact that any changes to the tables referenced by the indexed view will also be reflected in the indexed view.

Any INSERT, UPDATE, DELETE or MERGE query that affects the Product or TransactionHistory tables will have extra operations added to the execution plan to make the appropriate changes to our indexed view.

The query optimizer does not keep track of any tables that might be created by the user from the base tables - they will not be maintained to reflect base table changes, and any indexed views created on these new tables will reflect only changes to those tables, not the originals. There is no magic here - the relationship between an indexed view and its base tables is very explicit.

The thin table example

To take an example that appears to match your question, say we create a 'thin' extract from the Product base table, containing only the ProductID column:

CREATE TABLE dbo.ThinProduct
(
    ProductID integer NOT NULL

    CONSTRAINT PK_ThinProduct
    PRIMARY KEY (ProductID)
);

INSERT dbo.ThinProduct
    (ProductID)
SELECT
    p.ProductID
FROM Production.Product AS p;

Now we drop the original indexed view and create a new one that references the thin table instead:

DROP VIEW dbo.IV;
GO
CREATE VIEW dbo.IVthin
WITH SCHEMABINDING
AS
SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM dbo.ThinProduct AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
GROUP BY
    p.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.IVthin (ProductID);

A new query that references the thin Product table directly, can use the new indexed view:

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM dbo.ThinProduct AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

A query that references the original Product table cannot use this new indexed view, because there are no guarantees that IVthin will stay in step with any changes to the Product table (it will reflect changes to the ThinProduct table):

SELECT p.ProductID, cnt = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE
    p.ProductID BETWEEN 1 AND 100
GROUP BY
    p.ProductID;

The execution plan shows base table access - it cannot use the indexed view:

Summary

Indexed view matching can only be performed where the appropriate guarantees are enforced by the engine. The scenario outlined in the question cannot occur as outlined there. The SSIS package must be referencing indexed views that can be expanded or issuing queries that can be matched to an indexed view for a query reference to table1 to resolve to anything other than that named object.

I appreciate this answer may not help you except in a general sense, but the discussion around the question has gone on for quite some time without clear specifics emerging. The question could be made easier for answerers to address specifically by including the actual SSIS query, table and indexed view definitions, and actual execution plans.