Click here to Skip to main content
15,941,690 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Guys,

I have a table with some columns and an primarykey column as integer and auto identity (auto increament by 1).
I am trying to partition this table by moving the old data to another table.Thus I have created a view like this:

CREATE VIEW [dbo].[JoinTraps]
SELECT     *
From DB2.dbo.OldTable
SELECT     *
FROM    DB1.dbo.NewTable

(I have also define my check CONSTRAINT on my auto identity column.)

If I don't have Auto Identity every things work fine. but when I have it Insert and update fails!!
Is there any way to partition a table which has auto an identity column as primary key?!

(I have tested different check constraint on different columns but no success.)


The Identity column on the archive table doesn't make logical sense. The key should continue to be the one from the orignal table, otherwise relationships to other tables etc will go out of whack and audit information might be lost.
The other thing is that splitting the table like this should not be needed. If you are having performance problems, you should look at your database normalisation and index optimisation. By splitting this table into two you will cause normalisation problems: foreign key constraints will be effected, join conditions mucked up, and you will need have three "categories" of SQL over these tables( one for the new, one for the old and one for all).
If you must do what you are saying (e.g. someone is insisting on it) and you have some control over the DB design consider swapping the int primary key over to a GUID each of these is globally unique.
Share this answer
thatraja 8-Jan-11 1:30am    
good answer Mr.KB
Keith Barrow 10-Jan-11 13:04pm    
I may have expressed my point unclearly, the ID column should remain, but it shouldn't be an identity field (ie and auto-generated number) the value should come from the originating table. You should put a constraint on the table to ensure it is unique. I still think creating the archive table is a bad plan, see my reply.
aidin Tajadod 11-Jan-11 14:18pm    
Thanks Keith, So I assume that your answer is NO to my question. I mean there is no way to have both auto identity as auto number column and partitioning!! :(
Actually at first I did not have auto number. But to increase the performance I changed it. Now I think I have to change to what it was first if I want to partion it!!
Thanks to all you guys.
I reckon that you'd have to set the autoidentity counter to a start value that is one greater than the largest value in the old table. If your insert into the view there would already be a column with that same value in the primary key and that just can't be. When updating it would be the same thing if there were to be two columns with the same value in the primary key column.

If you set the start value for the autoidentity counter to 1 greater than the largest value in oldtable you should be fine.

Hope that helps!

Best Regards,
Share this answer
aidin Tajadod 10-Jan-11 13:07pm    
Thanks Manfred but it does not work! If I specify the identity column in my insert command I will get this error :
Cannot INSERT into partitioned view 'DB1.dbo.v1' because table 'DB2.[dbo].[t1]' has an IDENTITY constraint.
and if I don't do that I get this error:
Partitioned view DB1.dbo.v1' is not updatable because a value was not specified for partitioning column 'c_int'.
Try using INSTEAD OF INSERT trigger on the view, and then in it, write: INSERT INTO (active table name here) SELECT (columns) FROM INSERTED
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900