I need identity on updatable partitioned view

Ok, already read that is not allowed to have identity column in any member table of a updateable partitioned view.

But I need an identity column. Is there any other way or ide how to solve this problem?

This is one of the situation where using a column with a uniqueidentifier datatype, set to a default of newid() is the 'right' solution.|||It does not work...|||

I have to disagree with you on that!

What are you attempting to accomplish, and what steps have you taken so far.

Please include the TABLE and VIEW DDL.

|||

create table bileta_te_lozura_2007_01

(id int not null,

databiletes datetime not null check (databiletes>=cast('20070101' as datetime) and databiletes<cast('20070201' as datetime)),

constraint [pkjan2007] primary key clustered ([id],[databiletes]) with fillfactor=50 on [primary]

)

create table bileta_te_lozura_2007_02

(id int not null,

databiletes datetime not null check (databiletes>=cast('20070201' as datetime) and databiletes<cast('20070301' as datetime)),

constraint [pkshk2007] primary key clustered ([id],[databiletes]) with fillfactor=50 on [primary]

)

create view bileta_te_lozura

as

select * from bileta_te_lozura_2007_01

union all

select * from bileta_te_lozura_2007_02

I need ID to be an unique ID

For the whole view not only for one table!

|||

Try this:

CREATE TABLE bileta_te_lozura_2007_01
( ID uniqueidentifier DEFAULT newid() NOT NULL,
DataBiletes datetime NOT NULL CHECK ( DataBiletes >= cast( '20070101' AS datetime ) AND DataBiletes < cast( '20070201' as datetime )),
CONSTRAINT [pkjan2007] PRIMARY KEY CLUSTERED ([ID],[DataBiletes]) WITH FILLFACTOR = 50 ON [primary]
)

CREATE TABLE bileta_te_lozura_2007_02
( ID uniqueidentifier DEFAULT newid() NOT NULL,
DataBiletes datetime NOT NULL CHECK ( DataBiletes >= cast( '20070201' AS datetime ) AND DataBiletes < cast( '20070301' as datetime )),
CONSTRAINT [pkshk2007] PRIMARY KEY CLUSTERED ([ID],[DataBiletes]) WITH FILLFACTOR = 50 ON [primary]
)

CREATE VIEW dbo.bileta_te_lozura
AS
SELECT * FROM bileta_te_lozura_2007_01
UNION ALL
SELECT * FROM bileta_te_lozura_2007_02

|||

That will create an unique identifier column, not an integer column. Looking that all this is done because table can be be very large i'm affraid for performance issues using rowguid things...

I mean, if i query as follows:

select aa,bb,cc from bileta_te_lozura where id='233acf-sdffd-sdfsdfs-sdfsf-sdfssss'

have the same performance with:

select aa,bb,cc from bileta_te_lozura where id=1167876

Every table has some million rows.

Anyway... this also does not work. It report error during insert

Partitioned view 'renis.dbo.bileta_te_lozura' is not updatable because table '[renis].[dbo].[bileta_te_lozura_2007_01]' has a DEFAULT constraint.

|||

Ok Regis,

I'm starting to get a better idea about your situation. I think that if you do not have a primary key for the data, and that if you need to create a surrogate key, and since IDENTITY is not available in this situation, you may have to create a 'Table of Numbers' and get/increment the new value before each INSERT. One option would be having a Staging table, where the IDENTITY column does exist, and then immediately (after the IDENTITY value is assigned) move the data to your partitioned tables.

I also caution you that having a new table for every month will rapidly become burdensome. If you are using SQL 2005, you may wish to investigate table partitioning -and if you are not using SQL 2005, table partitioning may be the reason to upgrade. See Books Online, topic: [Partitioned Tables]

Distributed Partitioned Views (DPV) are best suited for multiple servers.

|||I tried and resolved this problem using partition tables... With partition views this was impossible, (not impossible, but just too long and complicated, which in generall means a lot of bugs)