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 |
|
Lavos
Posting Yak Master
200 Posts |
 Posted - 2003-01-22 : 15:49:54
|
| Hello, I've been gone for some time and haven't read the forums for like a month. I've been working with tables that have identity columns for the primary key. Unfortunately, the previous person wrote a lot of stuff using cursors and it worked out great for identity PK's. (I at least re-coded his States (ie. US states) to remove the identity :( )The problem is that there are other tables that have to be updated or inserted into with these keys generated from the identities. I have a crappy solution so far, but I know it's bad and I don't know enough about transaction isolation levels to know if I'm going to encounter problems or not if someone else is trying to simultaneously insert items into my tables.Right now, I store the maximum value in the identity column before the insert, do my insert, then insert into a temp table the values above the previously recorded maximum value. I then use this temp table to do my other work.Is there a better way to do this? I could try to remove the identity but there isn't a candidate key to take over.----------------------"O Theos mou! Echo ten labrida en te mou kephale!"So Long and Thanks for the Links. |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2003-01-23 : 02:00:22
|
| In fact there are lots of alternatives to an identity primary key column. It just depends on the DB design, stronghold of nerves and quantity of time which one to implement.I suggest that you open BOL on "DEADLOCKS" and see for yourself if your application is a potential candidate for it.BTW-you should find out where this previous fella lives and nail a dead rat to his door...(as a "thank you" note) |
 |
|
|
afterburn
Starting Member
28 Posts |
Posted - 2003-01-23 : 21:02:45
|
| I have the same type of issue but alot more than just that type of stuff. This guy built the whole business system around his crap. IE the values for ordered items are inserted into the table but when processing 2 of 4 items he does some wacky math on the taxes applied to it. total of the order was 50 say only 2 of the items shipped instead of applying taxes on the items that shipped he charges the card (valueOfShipped+shipping)/TaxValue in the order table so if the total of the items that shipped was 12.50 instead of (12.50+shipping)*taxrate he doesn't calculate the taxes per item shipped instead he calculates the taxes for the whole order/itemCount........???? Duplicates the same data serveral times across 4 tables. each table has its own prim ID and the child tables must be joined with the parent tables to insert which order the belong to.....??? all of them name arbitrailly....ID and the child table contains its on PK ID name ID, and relational Key is named orderID..... He has indexes but no FK or PK relationship defined anywhere.....Who hires these people??? |
|
|
|
scullee
Posting Yak Master
103 Posts |
Posted - 2003-01-23 : 22:05:13
|
| I had to mop up a problem for someone with a dodgy developer. This person didnt ever join tables so if they needed to get a product decription for an order she would make a separate SQL call for each line item.Dont even start me on the user of global variables to pass values between functions.First project i had to do with their work was taking 15 mins to process a 20 line item order. By the time i was finished it was taking 15 seconds and i really didnt spend too much time optimising.Where do these people come from!!!!!!!! |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2003-01-24 : 14:22:32
|
| Those people come from hell to torture us most likely. Or procedural programming backgrounds. (On the other hand, new procedural programmers with sql backgrounds can also be pretty bad.)rihardh, I fail to see where in the deadlock section of the bol I could find something to give advice about key selection. In this instance, there are no possible keys that could encompass the business rules. For instance, a rule that a customer could only have one order entered on a given day would enable those columns as a key, but that isn't the case. There is nothing to uniquely identify a given order until a sequencing field is introduced. I could then create my own formulas and functions to increment and calculate the values, but IDENTITY does all that for me, especially since I don't really care that there can be gaps.The real problem is that I don't like the idea of using cursors to solve this because it's nearly impossible to do a clean set based implementation for some of it.----------------------"O Theos mou! Echo ten labrida en te mou kephale!"So Long and Thanks for the Links. |
|
|
|
|
|
|
|
|
Multi-Row Insert and Identity columns