| Author |
Topic |
|
crobles
Starting Member
3 Posts |
 Posted - 2007-11-16 : 15:36:08
|
| I am developing an application that has a table with lots of records(network traffic) but the data is summarize every so often to create summary records (old records are deleted). The problem is that I have a PK based on an autoincrement ID (int) that will run out of numbers. However, this ID is not referenced anywhere, (not a foreign key from another table, not use for deletion and there is no update in this table whatsoever).So my possibilites are:1.- reseed the id when it is about to run out.2.- make the id bigint3.- remove the id and change the PK to 2 other fields4.- remove the id and without PK I am leaning toward option 4, because I do not see the need for a PK, but I understand that it is quite out of the normal.. So I would like to hear from other people ( I do not have much experience with DB). I also like option 3. I already have a index on one of the other fields (time). Any input will be appreciated.Claudio Robles |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-16 : 15:55:30
|
| All tables should have primary key constraints on them unless they are work tables. Yours does not sound like a work table. I would go with option 2 as it is the easiest. Option 3 is fine too though, just depends on what side of the surrogate vs natural key debate you are on. I change sides monthly.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
crobles
Starting Member
3 Posts |
Posted - 2007-11-16 : 16:38:16
|
| Tara, I am trying to make the app scalable and efficient and since this table can have 100 Million records I do not want to make it even bigger by having unused big fields. I guess I should take option 3 instead.Thanks for input.Claudio Robles |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-17 : 01:01:11
|
| Then you get a bigger pkey. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-11-18 : 17:40:20
|
| As rmiao indicated, I am not sure why you think the identity value will run out. If you genuinely don't need it then you should get rid of it. However as others have pointed out, you should have a PK. Have you considered other columns as candidates for a primary key (ip address/timestamp/packet number)? |
|
|
|
cr8nk
Yak Posting Veteran
77 Posts |
Posted - 2007-11-26 : 11:53:26
|
| The integer data type will take you up to 2,147,483,647 records. You should calculate the growth of your table and see if you will realistically hit the upper limit. If you need to change I would recommend using a big int over choosing additional columns. Depending on what your alternate key columns are, they are they could cause fragmentation and more difficult joins to other tables. |
|
|
|
crobles
Starting Member
3 Posts |
Posted - 2007-11-26 : 15:44:03
|
| Thanks for the answers.The app is creating records at a rate of about 120,000 per hour, which means that "integer" will last for about 3 years. However, the rate depends on the network traffic, so it can be significantly higher.My point is why do I need an additional large field (bigint) when so far I do not use the primary key at all? What is the problem of having a primary key composed of two other fields, and id from another table (int) and time (short time)? I pretty much always joint with the other table with this id.Thanks again. |
|
|
|
cr8nk
Yak Posting Veteran
77 Posts |
Posted - 2007-11-26 : 19:56:55
|
| If your primary key is a clustered index, it will maintain the ordering of the stored data based on columns of your primary key. A surrogate identity field works great for this because it doesn't have to reorder the data during inserts. If you have a ton of non sequential inserts with a composite key you should experience a performance hit. I suggest testing your composite key strategy vs a bigint identity key and see which performs faster. |
|
|
|
|
PK on a large table