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]

I just tried your code... and replaced CHECK constraints with

CHECK (businessDate >= '2004-10-01' AND businessDate < '2004-11-01')
CHECK (businessDate >= '2004-11-01' AND businessDate < '2004-12-01')
CHECK (businessDate >= '2004-12-01' AND businessDate < '2005-01-01')

Primary keys are still on (id, businessDate, price) - and INSERT into partitioned view works.

 

[sanjdhiman]

oki doki i will try and get back to you. what version of SQL Server are u using Enterprise? or Standard?

 

[vongrunt]

FYI I checked this on Personal and Standard.

Enterprise version is the only one that supports distributed views (partitions on different servers).

 

[sanjdhiman]

thanks vongrunt... for all ur help great stuff......

thanks...


also.. just a quick question.. i am partitioning off a table that is about circa 10mm rows. splitting them down to about 800,000 rows each table.

would that justify the use of using a locally distributed view?

thanks

sanj

 

[vongrunt]

800k rows is maybe a bit too small for partition, though performance boost also depends on application design. Just make sure that most of application queries use only last "active" or eventually very few last partitions (WHERE businessDate BETWEEN ...).

 

[sanjdhiman]

thanks Vongrunt

Another very quick question.. Im finding when i run a stored proc with the partitioned view it run 3 times slower than if I just used the table.

The parameters are all set to NULL except the business date which is between x and y date. So it is using the partitioned column.

see below

@currencyId smallint = null,
@referenceCurveEnum smallint = null,
@startDate smalldatetime = null,
@endDate smalldatetime = null,
@issuerId int = null,
@seniorityEnum smallint = null,
@assetTypeEnum smallint = null
as
/* Set date values if not supplied. */
if @startDate is null
select @startDate = '01-Jan-1998'
if @endDate is null
select @endDate = getdate()


/* Final select. */
select I.issuerId as "CMA Issuer Id",
E1.name as "Seniority",
I.name as "Issuer Name",
S.name as "Sector Name",
I.domicileRegionId as "Domicile",
R.cMA as "CMA Rating",
null as "Stock Ticker",
C.currency as "Currency",
E2.name as "Reference Curve",
T.businessDate as "Business Date",
E3.name as "Instrument Type",
dbo.IssuerFairValueSpread(I.issuerId, I.seniorityEnum, T.assetTypeEnum, T.currencyId, T.referenceCurveEnum, T.businessDate, 1) as "1Y Level",
dbo.IssuerFairValueSpread(I.issuerId, I.seniorityEnum, T.assetTypeEnum, T.currencyId, T.referenceCurveEnum, T.businessDate, 2) as "2Y Level",
dbo.IssuerFairValueSpread(I.issuerId, I.seniorityEnum, T.assetTypeEnum, T.currencyId, T.referenceCurveEnum, T.businessDate, 3) as "3Y Level",
dbo.IssuerFairValueSpread(I.issuerId, I.seniorityEnum, T.assetTypeEnum, T.currencyId, T.referenceCurveEnum, T.businessDate, 4) as "4Y Level",
dbo.IssuerFairValueSpread(I.issuerId, I.seniorityEnum, T.assetTypeEnum, T.currencyId, T.referenceCurveEnum, T.businessDate, 5) as "5Y Level",
dbo.IssuerFairValueSpread(I.issuerId, I.seniorityEnum, T.assetTypeEnum, T.currencyId, T.referenceCurveEnum, T.businessDate, 6) as "6Y Level",
dbo.IssuerFairValueSpread(I.issuerId, I.seniorityEnum, T.assetTypeEnum, T.currencyId, T.referenceCurveEnum, T.businessDate, 7) as "7Y Level",
dbo.IssuerFairValueSpread(I.issuerId, I.seniorityEnum, T.assetTypeEnum, T.currencyId, T.referenceCurveEnum, T.businessDate, 8) as "8Y Level",
dbo.IssuerFairValueSpread(I.issuerId, I.seniorityEnum, T.assetTypeEnum, T.currencyId, T.referenceCurveEnum, T.businessDate, 9) as "9Y Level",
dbo.IssuerFairValueSpread(I.issuerId, I.seniorityEnum, T.assetTypeEnum, T.currencyId, T.referenceCurveEnum, T.businessDate, 10) as "10Y Level",
T.veracity as "Veracity Index",
T.assetCount as "Number of Instruments",
T.derivationLevelEnum as "Derivation Score",
T.rSquared as "R Squared",
T.spreadDate as "Roll Days",
T.liquidity as "Liquidity",
null as "Capitalisation"
from Sectors S,
Issuers I,
IssuerTermStructures T,
Enumerations E1,
Enumerations E2,
Enumerations E3,
Currency C,
RatingRanks R

where T.currencyId = isnull(@currencyId, T.currencyId)
and T.referenceCurveEnum = isnull(@referenceCurveEnum, T.referenceCurveEnum)
and T.assetTypeEnum = isnull(@assetTypeEnum, T.assetTypeEnum)
and T.businessDate >= @startDate
and T.businessDate <= @endDate
and S.sectorId = I.sectorId
and S.seniorityEnum = I.seniorityEnum
and I.issuerId = isnull(@issuerId, I.issuerId)
and I.seniorityEnum = isnull(@seniorityEnum, I.seniorityEnum)
and I.issuerId = T.issuerId
and I.seniorityEnum = T.seniorityEnum
and E1.enumeration = T.seniorityEnum
and E1.type = 4 /* Seniority */
and E2.enumeration = T.referenceCurveEnum
and E2.type = 1 /* Benchmark Curve */
and E3.enumeration = T.assetTypeEnum
and E3.type = 3 /* AssetType */
and C.currencyId = T.currencyId
and R.ratingId =* I.ratingId
and T.derivationLevelEnum < 8

so lots of joining going on.....

T is the alias of the table. That is what I change to do some speed testing between the view and the table.

the table is so much faster... by three times

any ideas???

 

[vongrunt]

Purpose of partitioned views is to make "transparent horizontal partitioning". Ya know, typical data lives for a short time then goes to archive. Amount of working data is constant over time, archive data grows. Normally you can make working and archive set of tables, then move data from one to another when it's "life cycle" is over. Or in general, it doesn't make sense to have one single table containing data from 1992. when most of queries access data from last few months. Partitioned views do exactly that, but with minimal drone work.

The drawback is that queries over multiple partitions involve slow UNION. AFAIK there is nothing to do about that. As a compromise, you can change partitioning strategy by making them bigger (say, one per quarter).

Btw. SQL2k views can be slow enough even without partitioning

 

[sanjdhiman]

right ok thank you vongrunt.... sounds like no matter what the view will be slow....

thanks for all the help

sanj

 

[SQLSister]

Have you indexed tha view? That could improve performance.

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

 

[sanjdhiman]

the index didnt like my view....

it is a partitioned view

--the view

create VIEW IssuerTermStructureBindMonth

with schemabinding
AS
SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated
FROM dbo.IssuerTermStructuresDec04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresNov04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresOct04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresSept04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresAug04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresJul04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresJun04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresMay04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresApr04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresMar04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresFeb04

UNION ALL

SELECT issuerId,seniorityEnum,assetTypeEnum,currencyId,referenceCurveEnum,
businessDate,a,b,assetCount,rSquared,derivationLevelEnum,veracity,
liquidity,spreadDate,impliedRating,lastUpdated

FROM dbo.IssuerTermStructuresJan04





--the index
CREATE unique clustered index IDX_BIND_MONTH_2004 ON IssuerTermStructureBindMonth ([issuerId], [seniorityEnum], [assetTypeEnum], [currencyId], [referenceCurveEnum], [businessDate])

--the error msg
Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'History.dbo.IssuerTermStructureBindMonth'. It contains one or more disallowed constructs.


thanks
sanj

 

[vongrunt]

Indexed views don't like UNION

 

[sanjdhiman]

yep i thought as much.. oh well SQL Server did try heheh.. its ok. I think Indexed views on part. Views are not goin to work

guess i will have to upgrade to 2005 where partitioned data is done properly..

thanks again vongrunt and SQLSister for ur help

sanj