Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
snelson
Starting Member
10 Posts |
 Posted - 2004-11-21 : 00:09:54
|
| 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! |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-21 : 02:40:22
|
| INSTEAD OF trigger on the VIEW?Kristen |
 |
|
|
snelson
Starting Member
10 Posts |
Posted - 2004-11-21 : 02:51:29
|
| Hey Kristen thanks for the reply.How can i use a trigger to add an unique Id? Do you have any code for this or have a URL to a webpage that explains this? |
|
|
|
snelson
Starting Member
10 Posts |
Posted - 2004-11-21 : 03:02:01
|
| Kristen if you were building a web application that could potentially have thousands and thousands of real estate listings from each State. Would you design it so that the tables were horizontally partitioned by state from the get go?What is the best way to structure an application like this? I assumed the horizontal part. was a good way. But then i couldn't get the inserts working for the views.Could you expand a little on the user of triggers in this sort of application?Thanks so much. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-21 : 04:33:38
|
| I was thinking that the trigger could INSERT directly into the relevant table, using some IF/CASE logic.BoL explicitly states:INSERT statements are not allowed if a member table contains a column with an identity property.I would tend to use Horizontal Partitions where: o Massive number of rows o Data is added chronological (partition by Month or Year)You talk of thousands of rows, I personally wouldn't worry until it gets to tens of millions of rows. You may need to concentrate on optimisation of queries - but that's not such a bad thing anyway!Kristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-21 : 11:22:50
|
quote:
...horizontally partitioned on the stateId (Ex. where CA is a stateId of 1)
Why bother with a 4-byte, meaningless integer when you could use a 2-byte, standardized and easily recognizable U.S. Postal Service state code (CA)? |
|
|
|
snelson
Starting Member
10 Posts |
Posted - 2004-11-21 : 12:54:58
|
| Hey,It wasn't the partition column that was really important. Although you do make a good point. I guess I am not understanding the methods SQL has implemented to allow developers to horizontally partition an application from the start. So that it never becomes an issue.I don't understand how i can have a VIEW which allows INSERTS and UPDATES and still have an unique identity on each record (AKA Identity(1,1). This is what i can't get to work.If a group of developers know that an application is going to contain huge numbers of rows why should there not be a design architecture that is easily implemented for large applications? In my opinion if a PARTIONED VIEW had an option to include identities this would be perfect. What are my options?Can anyone comment?Thanks everyone! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-21 : 13:46:04
|
| Why are you horizontally partioning anyway? I would not partition tables based on the data itself (i.e., which state the transaction belongs to) but rather based on something like a transaction's "status" or whether it is "historical" or "archived" or "deleted." If the table is properly indexed, I don't see the benefit you would get from partioning in this manner. You would be over-complicating things, IMHO.- Jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-21 : 23:32:58
|
"In my opinion if a PARTIONED VIEW had an option to include identities this would be perfect"Well, BoL says you can't do that!"What are my options?"INSTEAD OF trigger - Didn't I say that already?  CREATE TRIGGER MyTrigger ON MyViewINSTEAD OF INSERTASINSERT INTO Listings_CA(StateId, UserId, ...)SELECT StateId, UserId, ...FROM insertedWHERE StateId = 1INSERT INTO Listings_XX(StateId, UserId, ...)SELECT StateId, UserId, ...FROM insertedWHERE StateId = 2... Kristen |
|
|
|
|
|
|
|
|
Horizontal Partition of states with identity?