November 20, 2004 at 11:29 pm
Hello,
I am building an application that will be used in all US states.
I have the tables divided like this, (Listings_CA, Listings_NY, Listings_FL... etc.)
column schema for above table Listings_CA (only difference
between tables is constraint)
"Id int IDENTITY(1,1)," +
"StateId INT NOT NULL PRIMARY KEY " +
"CONSTRAINT ListStateId_CHK1" CHECK (StateId = 1)," +
"UserId int NOT NULL," +
As you can see i am trying to design the app so that the tables are horizontally partitioned on the stateId (Ex. where CA is a stateId of 1).
I am using a view called Listings to bring them together using UNION ALL.
I want to be able to do an INSERT using the VIEW 'Listings' but it is failing because I have an Identity column in my table.
My question is... how can i get around this and still have an Id column for my Listings?
Thanks guys!
November 23, 2004 at 8:00 am
This was removed by the editor as SPAM
December 6, 2004 at 5:07 am
I think you have to read more about
(Instead of Insert) Triger
and put it on the view
so when you insert your data ... you check for State and the insert data in the Appropriate Table
does this help u?
Alamir Mohamed
Alamir_mohamed@yahoo.com
December 6, 2004 at 6:56 am
Vertical partioning, this is, hm? Horizontal = moving attributes (i.e., columns) to a new table, not moving rows. edit: Yoda a little tired from the weekend!
Why do you feel you must do this? Partioning can be done after the fact, should performance prove to suffer.
Should you feel that parition you still should do, your UNION view , composite virtual primary key of State/Identity it shall have.
Optimize only when needed for performance! To over-engineer, a quick path to the dark side!
December 6, 2004 at 7:15 am
what about freeing yourself from the identity column
and use a newe table that has the last ID number .. and when you want to insert .. get Last number + 1 and put it on the table you want (from the instead of View)
For Yoda, Vertical = moving attributes (i.e., columns) to a new table, not moving rows.
and I think Horzintal = Move Data .. and this problem talk about this .. am i right?
Alamir Mohamed
Alamir_mohamed@yahoo.com
December 6, 2004 at 7:36 am
oops.. yes, Yoda had it backwards. Long weekend, indeed!
Either way, unless a convincing reason is given, done this should not be!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply