Microsoft KB Archive/268361

= Effects of moving a database from SQL Server 2000 Enterprise Edition to SQL Server 2000 Standard Edition =

Article ID: 268361

Article Last Modified on 12/15/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q268361



SUMMARY
Moving or copying a database across various Microsoft SQL Server 2000 editions (such as Desktop Engine, Developer, Personal, Standard, Enterprise) is a seamless process and is independent of the edition of the server as far as the stored data is concerned. However, there are some feature differences between editions that may introduce variation as to how the stored data is used. Microsoft SQL Server 2000 Standard Edition includes the majority of features and functionality that Microsoft SQL Server 2000 Enterprise Edition (EE) provides. However, Microsoft SQL Server 2000 Developer Edition and Enterprise Edition contain edition-specific features not available in the other editions of the product.

Following are some of the features that these two unique editions support, the first two of which are discussed in further detail later in this article:
 * Indexed views
 * Distributed Partitioned views
 * Parallel DBCC
 * Parallel CREATE INDEX
 * Enhanced Read-Ahead and Scan
 * Log Shipping

Because Indexed views and updateable Distributed Partitioned views are features available only in the SQL Server 2000 Enterprise Edition, the question is raised of what the consequences are of moving a SQL Server 2000 EE database that contains such objects to a Standard Edition SQL Server 2000. For this discussion, the methods for moving a database are assumed to include Backup/Restore, by using the sp_detach_db and sp_attach_db stored procedures, and by using the Copy Database Wizard.



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:

270054 PRB: Indexed Views can be Created on All Versions of SQL Server 2000

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:

&quot;The query processor first uses OLE DB to retrieve the CHECK constraint definitions from each member table...

The query processor compares the key ranges specified in an SQL statement WHERE clause to the map showing how the rows are distributed in the member tables. The query processor then builds a query execution plan that uses distributed queries to retrieve only those remote rows needed to complete the SQL statement. The execution plan is also built in such a way that any access to remote member tables, for either data or meta data, are delayed until the information is required.&quot;

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:

Server: Msg 4451, Level 16, State 19, Line 1

Views referencing tables on multiple servers are not updateable on this SKU of SQL Server.

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.