| Author |
Topic |
|
raju2647
Starting Member
22 Posts |
 Posted - 2004-10-11 : 05:30:46
|
| hai I am currently in designing phase of DB.i would like to know the benefits of using autonumbering for PK field.Will it cause future problems like updation,deletion...SHould i stick to incremention using a stored procedure.waiting for replyregardsRaju2647 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-11 : 05:43:00
|
you need to weigh the pros and conspros on autonumbering:1. no need to keep track if insert into the table will produce duplicate value errorcons on autonumbering:1. what would be your highest number? the datatype you've chosen may reach it's limitation2. you cannot recycle the number should deletion occur3. the last number does not reflect the total number of rows in the table4. due to deletions, skipped numbers may occurpros on sproc increment1. all cons of autonumberingcons on sproc increment1. pros of autonumberingi'm a bit biased here but when it comes to relationships, i have a hard time of trying to relate a number in one table to another number in another table. IMHO, PK should be a little more descriptive, not just a series of numbers. |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-11 : 08:23:07
|
well i'm for autonumbering.highest number is way high if int is used, so there is no worry of reaching max limit.other that that i agree with our sleeples beuty (jen )i wouldn't use manual numbering... can get really messed up if not implemeted properly.if you'd tell us what kind of db you're designing, there are people here that can help you more precisely.Go with the flow & have fun! Else fight the flow  |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-11 : 22:15:02
|
| hi spirit, concerning with int,i'm doing this DTS and tables have the same DDL, but then it stops after 3 million and tells me it can't insert a null. i checked the PK (autonumber ofcourse, inherited you know) and there's a value. good thing i thought of archiving to a staging table first before transferring to the real archive table. now my dilemma is, the data is still stuck there. i tried changing the int to bigint, after a few hours of conversions, finally had it. but when i ran the dts again, same error. any idea? |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-10-12 : 04:16:12
|
| Jen, are You sure that the "PK (autonumber ofcourse, inherited you know)" is the culprit !?I find that very hard to believe...Maybe You just have a null value in some other column.If the ddl is the same as you say, double check the nullability of the columns.rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-12 : 04:27:03
|
| hi rockmoose, yap there's a value alright, i even double checked it before. but you know what? i ran the query again, and it worked without me changing anything from last status.just for discussion's sake, i'm migrating several millions of data from one table to another for archiving. so when i perform dts, i just copy the table as is, same pk, same columns... it's important that i preserve the pk (even if it's autonumber).then while i'm at around 3 million +, server tells me it's unable to insert null value on the pk field (not seeded), but checking it, there is a value.so i checked what's wrong and assumed that the size has reached it's limits, so i increase int to bigint. i tried for two days, still no dice. then just this morning, i tried again, wishing my fairy godmother hacked into the servers and did something to my package, and voila! today it worked.now i'm thinking, what could have gone weird? next month i'll monitor the process if it will "happen" again...--------------------keeping it simple... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-10-12 : 04:34:34
|
| Miracles happen even with D T S !I try to avoid it as far as possible, and stick to sql.If you link the servers and use sql, would that be an alternative for you ?Also, you could use bcp(out) + bulk insert, would be intersesting to see if there is a performance gain ?rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-12 : 04:43:38
|
| due to some weird security issues we have and coz the link was too slow, i prefer not to use link servers directly and file transfers, the dts packages were created through design and not from wizard though, which gave me enough control over what happens except for that pk stuff...lol i've been reading about bcp and yes it will be nice to see performance improving since hundreds of records are processed per second. I'll schedule that for my next project. right now, i'm automating everything as fast as i can, then later on, just make the improvements. when i do get into a sticky situation, i'll ask you guys here for help...--------------------keeping it simple... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-10-12 : 06:17:56
|
quote:
due to some weird security issues we have and coz the link was too slow
for performance, make sure that the linked server uses "use remote collation" that resolved some performance issues for us.As for security: we have found Integrated Security doesn't propagate well with linked servers.A problem it seems everyone has...rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-10-12 : 06:33:44
|
| Oh, raju2647... forgot about the thread, sorry.If you use an identity (autonumber) for PK, make sure you have at least one candidate key on the table.( unique index ).The reason is that an identity isn't really a KEY in relational terms, it makes sure that each row has a unique number, but duplicate data could occur. ( same data, different autonumbers ).Autonumbers are practical IMO, and easy to work with.Some would argue the opposite, I am sure.There are pros&cons, and what is best depends on the type of system you are building as well ...There has been discussions on this issue:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38582[/url][url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38547[/url][url]http://sqlteam.com/forums/topic.asp?TOPIC_ID=38559[/url]rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
Bitz
Starting Member
19 Posts |
Posted - 2004-10-13 : 17:00:26
|
| And just so another voice is heard...We actually abhor the use of Autonumbering and use a Stored Proc instead. :) One of the primary reasons we used it was that we gained the flexibility of making our keys increment by whatever amount we wanted. (For example, one of our tables incremented the PK by 121 every time a new row was created.)If we ever use IDENTITY, it is only for Tally-type tables. :)O |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-10-13 : 17:14:48
|
| If the DTS package is written properly in will be using BULK INSERT already.Actually I find autonumbering extremely irritating when it comes to conversion/migration scenarios.Bitz, the identity property in SQL Server can be incremented by any INT/BIGINT? (negatives included) number.DavidM"Always pre-heat the oven" |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-10-13 : 17:57:08
|
| I can't think of a reason why a stored procedure should be used to fake an identity key. Making an identity key on the table performs the same task with less work.Also one thing that you can't do with the stored procedure method that you can do with a identity key is insert multiple records with a single statement. Dustin Michaels |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-14 : 00:49:28
|
| I stick to Identity keys as long as I don't need to reset or reuse the primary key. I find it far more convenient than using a Sproc or otherwise.Couple of instances where I can't use Identity is if I have a two part key and one needs to be reset at intervals; ex : Key = CTR1+CTR2, where ctr2 increments from 0 to 100 and resets back to 0 and then CTR1 is incremented.Hemanth GorijalaBI Architect / DBA...Exchange a Dollar, we still have ONE each.Exchange an Idea, we have TWO each. |
|
|
|
|
Autonumbering