Solved

Can you fix the bug in "Partitioned VIEWS" ?

Posted on 2006-11-11
8
295 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
  • 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now