Solved

Can you fix the bug in "Partitioned VIEWS" ?

Posted on 2006-11-11
8
301 Views
Last Modified: 2008-01-09
-- A partitioned view joins data that is horizontally split.

/* Create an employees table for employees 100 - 199 */

USE Sample
GO

CREATE TABLE dbo.Employees_1
(
    EmpID [tinyint] IDENTITY (100, 1) PRIMARY KEY CHECK (EmpID BETWEEN 100 AND 199),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)
GO

INSERT INTO Employees_1 VALUES ('Peddi', 'Kishore', '01/01/2006')
GO

INSERT INTO Employees_1 VALUES ('Ponnaganti', 'Lohiya', '02/02/2006')
GO

INSERT INTO Employees_1 VALUES ('Peddi', 'Suresh', '03/03/2006')
GO

INSERT INTO Employees_1 VALUES ('Kommineni', 'Swapna', '04/04/2006')
GO

/* Create an employees table for employees 200 - 299 */

USE Sample
GO

CREATE TABLE dbo.Employees_2
(
    EmpID [tinyint] IDENTITY (200, 1) PRIMARY KEY CHECK (EmpID BETWEEN 200 AND 299),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)
GO

INSERT INTO Employees_2 VALUES ('Mokkapati', 'Sreenivas', '01/01/2006')
GO

INSERT INTO Employees_2 VALUES ('Dhanekula', 'Tagore', '02/02/2006')
GO

INSERT INTO Employees_2 VALUES ('Singam', 'Ramesh', '03/03/2006')
GO

INSERT INTO Employees_2 VALUES ('Buddi', 'Ravi', '04/04/2006')
GO

/* Create the view on same server */

USE Sample
GO

CREATE VIEW Employees_View

AS

SELECT * FROM dbo.Employees_1

UNION ALL

SELECT * FROM dbo.Employees_2

GO

-- Run the view

SELECT * FROM Employees_View

GO

-- Insert / Update / Delete => view    //ERROR

UPDATE Employees_View

SET   LastName = 'Suman',
      FirstName = 'Chundi',
      BirthDate = '01/01/2006'

WHERE EmpID = 101

GO

-- Run the view

SELECT * FROM Employees_View

GO

When i have done the following command...i am getting one error:
==========================================

-- Insert / Update / Delete => view

UPDATE Employees_View

SET   LastName = 'Suman',
      FirstName = 'Chundi',
      BirthDate = '01/01/2006'

WHERE EmpID = 101

GO

ERROR:
=====

UNION ALL view 'Employees_View' is not updatable because a partitioning column was not found.

Can you modify the above code so that it will work fine !!
0
Comment
Question by:kishore_peddi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 17924032
i think you cannot use identity column as partitioned column,
try changing the tables definition as follows

CREATE TABLE dbo.Employees_1
(
    EmpID [tinyint] not null PRIMARY KEY CHECK (EmpID BETWEEN 100 AND 199),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)

and
CREATE TABLE dbo.Employees_2
(
    EmpID [tinyint] not null PRIMARY KEY CHECK (EmpID BETWEEN 200 AND 299),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)


then create the view and test again
0
 
LVL 39

Expert Comment

by:appari
ID: 17924036

Fromthe following link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp

Conditions for Creating Partitioned Views

SELECT list
All columns in the member tables should be selected in the column list of the view definition.


The columns in the same ordinal position of each select_list should be of the same type, including collations. It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.
Also, at least one column (for example <col>) must appear in all the SELECT lists in the same ordinal position. This <col> should be defined such that the member tables T1, ..., Tn have CHECK constraints C1, ..., Cn defined on <col> respectively.

Constraint C1 defined on table T1 must follow this form:

C1 ::= < simple_interval > [ OR < simple_interval > OR ...]
< simple_interval > :: =
    < col > { < | > | <= | >= | = }
    | < col > BETWEEN < value1 > AND < value2 >
    | < col > IN ( value_list )
    | < col > { > | >= } < value1 > AND
        < col > { < | <= } < value2 >

The constraints should be such that any given value of <col> can satisfy at most one of the constraints C1, ..., Cn so that the constraints should form a set of disjointed or non-overlapping intervals. The column <col> on which the disjointed constraints are defined is called the 'partitioning column.' Note that the partitioning column may have different names in the underlying tables. The constraints should be in an enabled state in order for them to meet the above conditions of the partitioning column. If the constraints are disabled, re-enable constraint checking with either the WITH CHECK option or the CHECK constraint_name options of ALTER TABLE.
Here are some examples of valid sets of constraints:

{ [col < 10], [col between 11 and 20] , [col > 20] }
{ [col between 11 and 20], [col between 21 and 30], [col between 31 and 100] }

The same column cannot be used multiple times in the SELECT list.
Partitioning column
The partitioning column is a part of the PRIMARY KEY of the table.


It cannot be a computed, identity, default, or timestamp column.


If there is more than one constraint on the same column in a member table, SQL Server ignores all the constraints and will not consider them when determining whether or not the view is a partitioned view. To meet the conditions of the partitioned view, there should be only one partitioning constraint on the partitioning column.


There are no restrictions on the updatability of the partitioning column.
0
 

Author Comment

by:kishore_peddi
ID: 17924128

-- A partitioned view joins data that is horizontally split.

-- A local partitioned view has all underlying tables in the same instance of SQL Server.  

/* Create an employees table for employees 100 - 199 */

USE Sample
GO

CREATE TABLE dbo.Employees_1
(
    EmpID [tinyint] NOT NULL PRIMARY KEY CHECK (EmpID BETWEEN 100 AND 199),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)
GO

INSERT INTO Employees_1 VALUES (101, 'Peddi', 'Kishore', '01/01/2006')
GO

INSERT INTO Employees_1 VALUES (102, 'Ponnaganti', 'Lohiya', '02/02/2006')
GO

INSERT INTO Employees_1 VALUES (103, 'Peddi', 'Suresh', '03/03/2006')
GO

INSERT INTO Employees_1 VALUES (104, 'Kommineni', 'Swapna', '04/04/2006')
GO

/* Create an employees table for employees 200 - 299 */

USE Sample
GO

CREATE TABLE dbo.Employees_2
(
    EmpID [tinyint] NOT NULL PRIMARY KEY CHECK (EmpID BETWEEN 200 AND 299),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)
GO

INSERT INTO Employees_2 VALUES (200, 'Mokkapati', 'Sreenivas', '01/01/2006')
GO

INSERT INTO Employees_2 VALUES (201, 'Dhanekula', 'Tagore', '02/02/2006')
GO

INSERT INTO Employees_2 VALUES (202, 'Singam', 'Ramesh', '03/03/2006')
GO

INSERT INTO Employees_2 VALUES (203, 'Buddi', 'Ravi', '04/04/2006')
GO

/* Create the view on same server */

USE Sample
GO

CREATE VIEW dbo.Employees_View

AS

SELECT * FROM dbo.Employees_1

UNION ALL

SELECT * FROM dbo.Employees_2

GO

-- Run the view

SELECT * FROM Employees_View
GO

-- Insert / Update / Delete => view

UPDATE Employees_View

SET   LastName = 'Suman',
      FirstName = 'Chundi'

WHERE EmpID = 101

GO

-- Run the view

SELECT * FROM Employees_View
GO



NOTE:
====

Still got the same error !! Can you please check ?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 28

Expert Comment

by:imran_fast
ID: 17928268
hi kishore_peddi ,

Your code is fine there is nothing wrongn in it but check the version of sql you have.

select @@version  

Note: You can modify data through a partitioned view only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft. SQL Server 2000 Developer Edition.

Regards

Imran

0
 

Author Comment

by:kishore_peddi
ID: 17935213
This is what i am having on my machine...


Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
      Dec 17 2002 14:22:05
      Copyright (c) 1988-2003 Microsoft Corporation
      Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Thanks,
Kishore
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17936266
hi kishore_peddi,
do you have latest service pack 4 for sql 2000.
0
 

Author Comment

by:kishore_peddi
ID: 17943415
Do we need service pack 4 for sql 2000 ?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17946108
Its always better to have latest service pack installed.
You can download it from microsoft site for free.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question