Effects of moving a database from SQL Server 2000 Enterprise Edition to SQL Server 2000 Standard Edition (268361)

MORE INFORMATION

Indexed Views

The indexes that support indexed views in SQL Server 2000 Enterprise Edition are maintained on the non-EE SQL Server. Running the sp_helpindex stored procedure on the view reports the existence of an index on both editions. However, the optimizer does not make use of the index when determining a query plan.

For example, the following query is run against the Pubs database on SQL Server 2000 EE, after which the database is copied to SQL Server 2000 Standard Edition and the query is run again:

USE pubs
DROP TABLE myt
go

--Create table
CREATE TABLE myt (c1 INT NOT NULL, c2 CHAR(25))
go
DECLARE @i INT
SET @i = 1
WHILE @i < 10000
	BEGIN
	INSERT INTO myt VALUES(@i,'a')
	SET @i = @i +1
	END
go

--Create view on myt table
SET quoted_identifier ON
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myv]') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW myv
go
CREATE VIEW myv 
with SCHEMABINDING 
as 
SELECT SUM(c1) as sumc1, count_big(*) as countbig, c1 FROM dbo.myt GROUP BY c1
go
SET quoted_identifier OFF
go

--Create index on the view
SET quoted_identifier ON
SET arithabort ON 
go
CREATE UNIQUE CLUSTERED INDEX ncind ON myv (c1)
go
				

The query plan output that follows is generated from SQL Server 2000 Enterprise Edition after you run the following SELECT statement:

--Observe the use of clustered index on the view
SET SHOWPLAN_TEXT ON
go
SELECT * FROM myv
go
SET SHOWPLAN_TEXT OFF
				

StmtText                                                        
--------------------------------------------------------------- 
  |--Clustered Index Scan(OBJECT:([pubs].[dbo].[myv].[ncind]))

(1 row(s) affected)

				

When you run against the SQL Server Standard Edition, the same SELECT statement yields the following results:

StmtText                                                                                                
------------------------------------------------------------------------------------------------------- 
  |--Hash Match(Aggregate, HASH:([myt].[c1]) DEFINE:([Expr1002]=SUM([myt].[c1]), [Expr1003]=COUNT(*)))
       |--Table Scan(OBJECT:([pubs].[dbo].[myt]))

(2 row(s) affected)
				

Note that the Enterprise edition query processor makes use of the clustered index, while the Standard edition query processor scans the table and ignores the index on the view. For additional information about indexed views across different editions of SQL Server, click the article number below to view the article in the Microsoft Knowledge Base:

Distributed Partitioned Views

Similar to indexed views, the distributed partitioned views take advantage of the extended functionality of the SQL Server 2000 Enterprise Edition query processor. Here is the relevant section in SQL Server 2000 Books Online: This functionality is limited if the database is moved to a SQL Server Standard Edition. The query processor in Standard edition allows the use of the CHECK constraint for an execution plan to select data from a remote table. However, the SQL Server Standard Edition does not allow updates that are attempted through the view.

/***On SERVER2****/ 
sp_addlinkedserver 'server1'

--On Server2 create a partitioned table

if exists (select * from dbo.sysobjects where id = object_id(N'[customer_66]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [customer_66]
GO

CREATE TABLE [customer_66] (
	[CustomerID] [int] NOT NULL ,
	[name] [char] (20) ,
	[CHK] [int] NOT NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[CustomerID]
	)  ON [PRIMARY] ,
	CONSTRAINT [customeridck] CHECK ([Customerid] >= 321 and [Customerid] <= 520)
) ON [PRIMARY]
GO

/***On SERVER1****/ 

sp_addlinkedserver 'server2'

-- On Server1 create the partitioned table:

if exists (select * from dbo.sysobjects where id = object_id(N'[customer_33]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [customer_33]
GO

CREATE TABLE [customer_33] (
	[CustomerID] [int] NOT NULL ,
	[name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CHK] [int] NOT NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[CustomerID]
	)  ON [PRIMARY] ,
	CONSTRAINT [customeridck] CHECK ([Customerid] >= 0 and [Customerid] <= 320)
) ON [PRIMARY]
GO

--Create view on both servers

CREATE VIEW Customers AS
   SELECT * FROM server1.dpv.dbo.Customer_33
UNION ALL
   SELECT * FROM  server2.dpv.dbo.Customer_66

--On Server1 execute a select 

SELECT * FROM Customers

--On Server1 run the following

SET XACT_ABORT ON
GO
INSERT INTO customers VALUES (502,'Text Here', 502)
GO
UPDATE CUSTOMERS SET NAME = 'New Text' WHERE CHK = 502
GO
DELETE customers WHERE chk =502
				

The preceding Transact-SQL example runs successfully on the SQL Server Enterprise Edition. However, when run on the SQL Server 2000 Standard Edition, the INSERT, UPDATE, and DELETE statements fail with the following error message: NOTE: Take into consideration the following information when you copy or move the database across servers with respect to Partitioned views:

• After the move new linked servers have to be added to reflect the new server names. See the sp_addlinkedserver topic in SQL Server Books Online.
• The partitioned views have to be re-created to reflect the new servers to which the databases have been copied.
• The Microsoft Distributed Transaction Coordinator (MSDTC) service must be started on both computers when you run updates, inserts, and deletes.

REFERENCES

For more information about Partitioned views see:

SQL Server 2000 Books Online: topics: "Designing Partitions"; "Creating a Partitioned View"; "Designing Applications to Use Federated Database Servers"