is not updatable because a partitioning column 1

[sanjdhiman]

Hi

Im new to partitioned Tables and views

I have set up my tables in the following way

CREATE TABLE [dbo].[dec04] (
[Id] [int] NOT NULL ,
[businessDate] [smalldatetime] NOT NULL
CHECK (DATEPART(mm, businessDate) = 12) ,
[price] [smallint] NOT NULL
) ON [PRIMARY]

I do this for 2 other tables [nov04] and [oct04].

I add in my COMPOUND primary key, which is based on id, businessdate and price.

Check constraint is on one of the Primary key members so should be ok.

Next I create a view which UNIONs up all these tables

CREATE VIEW all AS
select *
from dec04

UNION ALL

select *
from nov04

UNION ALL

select *
from oct04

GO

Ok that is fine too.

Then when i go to insert data into the view using the following

INSERT INTO all VALUES (1, '12-01-04', 2)

This doesnt work it gives me the following error message, i have searched google for it but no luck. Please help thanks.

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

I tried to update the view - refresh it using sp_refreshview. but no luck.....
thank you for your help in advance
Sanj


GO

 

[vongrunt]

Not all versions of SQL Server support partitioned views... what is your version (personal, standard, enterprise, blah)?

 

[sanjdhiman]

the version im using is SQL 2000 Standard Edition.....

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Does Standard Edition let me insert into a View?

Thanks

 

[SQLSister]

There are specific rules concerning whether a particitoned view is updateable.

INSERT Statements
INSERT statements add data to the member tables through the partitioned view. The INSERT statements must adhere to these rules:

All columns must be included in the INSERT statement even if the column can be NULL in the base table or has a DEFAULT constraint defined in the base table.

The DEFAULT keyword cannot be specified in the VALUES clause of the INSERT statement.

INSERT statements must supply a value that satisfies the logic of the CHECK constraint defined on the partitioning column for one of the member tables.

INSERT statements are not allowed if a member table contains a column with an identity property.

INSERT statements are not allowed if a member table contains a timestamp column.

INSERT statements are not allowed if there is a self-join with the same view or any of the member table.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[vongrunt]

SQLSister: in this case it looks like DATEPART(mm, businessDate) is partitioning "column" - and primary key is composite. Will this work at all?

 

[SQLSister]

So have you defined the partitioning column? There are very specific rules about that too.

Partitioning Column Rules
A partitioning column exists on each member table and, through CHECK constraints, identifies the data available in that specific table. Partitioning columns must adhere to these rules:

Each base table has a partitioning column whose key values are enforced by CHECK constraints. The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table. Any given value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.

The partitioning column must be in the same ordinal location in the select list of each SELECT statement in the view. For example, the partitioning column is always the first column in each select list, or the second column in each select list, and so on.

Partitioning columns cannot allow nulls.

Partitioning columns must be a part of the primary key of the table.

Partitioning columns cannot be computed columns.

There must be only one constraint on the partitioning column. If there is more than one constraint, SQL Server ignores all the constraints and will not consider them when determining whether or not the view is a partitioned view.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[sanjdhiman]

so do u think from the composite key I have followed B.O.L. Which I think I have.

 

[sanjdhiman]

I have checked again and I still cant see anything wrong with what I have (see first message on this thread). There are no identities on the tables. There are indexes on the columns (Non-clustered).. but still dont think this will make a difference.

Thanks in advance

Sanj

 

[SQLSister]

What is the check constraint you have defined onthe partitioning field?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[vongrunt]

You are using DATEPART() to get partitioning part of a column. Maybe that's the problem. Personally I always used atomic column for partitioning - without problems.

Besides, what should happen after 12 months? Hint: CHECK constraint ignores year...

 

[sanjdhiman]

the following is the constraint I am using when creating the table

ALTER TABLE [dbo].[Nov04] ADD CONSTRAINT CHK_BusinessDateNovIT
CHECK (datepart([month], businessdate) = 11 AND datepart([year],


so that is how i add it. I did try the normal BETWEEN datex AND datey.. but that didnt work either

thanks guys for ur help.

 

[SQLSister]

That doesn't seem complete somehow. datepart([year]?

As a suggestion, maybe you could create an integer column to store just the month and then define the check constraint on that colunmn?

Or instead of using datepart use >=11/1/2004 and <12/1/2004

Is your date field part of the primary key?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[sanjdhiman]

oops here it is in full

ALTER TABLE [dbo].[Nov04] ADD CONSTRAINT CHK_BusinessDateNovIT
CHECK (datepart([month], businessdate) = 11 AND datepart([year], businessdate)=2004)


i will try what you have suggested and see if it works out ok.....

 

[sanjdhiman]

Note:

in the Example in B.O.L. they use the datepart in there example..

so i dont think its a major problem with the datepart

 

[vongrunt]

Dunno... I'm searching BOL and see only examples with CustomerID (single int column).

Simple test: can you modify constraint to something like

CHECK businessDate >= '2004-12-01' AND businessDate < '2005-01-01'

If my blind guessing is correct, this should work.

 

[sanjdhiman]

I will try that for u Vongrunt.. and see what happens.. cheers....


FYI In B.O.L. They have example as below:-

CREATE TABLE May1998Sales
(OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL,
CHECK (DATEPART(mm, OrderDate) = OrderMonth)
)

 

[vongrunt]

I found this example... isn't here OrderMonth partitioning column?

 

[sanjdhiman]

yep .. i looked at the order month partition column.. but i dont think i need that column?

what do u think

 

[vongrunt]

I'll check this and post results later today. Right now some people are killing me with yearly reports and similar cute stuff

 

[sanjdhiman]

no problem Vongrunt..

Also i read on google groups that the smalldatetime field is not liked for Partitioned Views..

For example some guy kept getting table scans.. be it small scans they were scans on tables that didnt meet his search criteria so as a PRECAUTION i changed my smalldatetime field to datetime.

I do get scans
when my WHERE clause is built up like the following:

WHERE datepart(month, businessdate) = 5 AND datepart(year, businessdate) = 2004

(This fits the check clause correctly.. but obviously based on the results below it doesnt work right....)

Yet another problem so my partitions are not working correctly yet and nor are my inserts.. Please help.

Table 'IssuerTermStructuresDec04'. Scan count 4, logical reads 20761, physical reads 0, read-ahead reads 20766.
Table 'IssuerTermStructuresNov04'. Scan count 4, logical reads 11500, physical reads 0, read-ahead reads 11502.
Table 'IssuerTermStructuresOct04'. Scan count 4, logical reads 10962, physical reads 0, read-ahead reads 10964.
Table 'IssuerTermStructuresSept04'. Scan count 4, logical reads 10595, physical reads 0, read-ahead reads 10597.
Table 'IssuerTermStructuresAug04'. Scan count 4, logical reads 7600, physical reads 0, read-ahead reads 7601.
Table 'IssuerTermStructuresJul04'. Scan count 4, logical reads 7391, physical reads 0, read-ahead reads 7391.
Table 'IssuerTermStructuresJun04'. Scan count 4, logical reads 7477, physical reads 9, read-ahead reads 7477.
Table 'IssuerTermStructuresMay04'. Scan count 4, logical reads 7135, physical reads 0, read-ahead reads 7142.
Table 'IssuerTermStructuresApr04'. Scan count 4, logical reads 7476, physical reads 0, read-ahead reads 7477.
Table 'IssuerTermStructuresMar04'. Scan count 4, logical reads 7814, physical reads 0, read-ahead reads 7815.
Table 'IssuerTermStructuresFeb04'. Scan count 4, logical reads 6797, physical reads 0, read-ahead reads 6798.
Table 'IssuerTermStructuresJan04'. Scan count 4, logical reads 7476, physical reads 0, read-ahead reads 7479.
Table 'Worktable'. Scan count 1, logical reads 512285, physical reads 0, read-ahead reads 0.

thanks

Sanj