| Author |
Topic |
|
ramdas
Posting Yak Master
181 Posts |
 Posted - 2003-03-07 : 08:50:12
|
| Hi, I have a column defined as integer with identity set on it. What happens when the max value for an int is reached, would the value get set to 1 again. For example I have a column called RecordID defined as int with identity (1,1).Any thoughts,ByeRamdas NarayananSQL Server DBA |
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-03-07 : 09:35:04
|
Why Not test it yourself? CREATE TABLE IdentTest (id int identity (1,1), val char(1))INSERT INTO IdentTest (val) values ('a')SELECT * FROM IdentTest DBCC CHECKIDENT (IdentTest, RESEED, 2147483647)DBCC CHECKIDENT (IdentTest)INSERT INTO IdentTest (val) values ('a')DROP TABLE IdentTest |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-07 : 10:10:13
|
| Well, do you expect to have 2 billion rows in that table? If you do, you'll encounter many other more serious issues long before the identity value becomes one. |
|
|
|
ramdas
Posting Yak Master
181 Posts |
Posted - 2003-03-07 : 11:48:21
|
| Thanks for replies folks.ByeRamdas NarayananSQL Server DBA |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-07 : 13:17:53
|
| 2,147,483,647It's quite common to hit this problem (telecom rating systems, tracking web site hits, ...).You can also change it to decimal(38,0) or bigint which will give a greater range.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-03-07 : 13:25:26
|
| You could set your seed to -2 billion to start. That would allow you to have about 4 billion values.4 billion rows is a lot of rows in my book.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-07 : 13:41:02
|
| And for a table that has just the identity column, when all values have been used up, would be a 16 gig table.That's a lot of data. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-07 : 13:55:55
|
That was pretty much what I was getting at. Anyone who wants to keep 2 billion+ rows in a single 16GB+ table is gonna have a lot of other things to worry about than the upper limit of an int value.  |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-07 : 14:11:00
|
| You don't have to keep all the data in the table.e.g. telecom rating systeminsert callsrate themdelete anything over 2 months old (for monthly billing)call table never has more than 2 months data but ID keeps increasing.This can soon reach the limit of an int without having a large table.(Especially in testing).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-07 : 14:42:06
|
| In that case, why not use a natural key composite. Phone Number and Call DateTime. My original exposure (how many years ago was that?) was to DB2 Mainframe. No identity columns there. Seems to be a lot of over-reliance on Identity column and not enough on data modeling.MOOBrett8-) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-07 : 15:06:51
|
| That key would cause the index to fragment.(As well as not always being valid depending on the switch).An identity would insert sequentially so be more efficient for speed and space.Sometimes the natural key can't be used due to implementational constraints.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-03-07 : 15:12:06
|
 ..seat belts on...and return your seats and trays to the upright and locked position.Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-07 : 15:38:00
|
| Nigel,Thanks for the advice. Would partitioning the data help with something that large. Even if you had an Identity PK, wouldn't you need an index to query the phone number as well, causing the same fragmentation? Also wouldn't you have a maint window to rebuild the indexes? If the info is flooding, maybe have a stage table with no keys or indexes, then have a process to "roll" them off in to another table? Never worked with anything that large in an OLTP database.Sitka, why so shocked? There's a lot that I don't know.Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-07 : 15:52:42
|
| Sitka isn't referring to you but referring to the heated debates about the use of identity columns vs. natural key. Do a search on this and you'll see!!!Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-07 : 16:00:14
|
| Thanks Tara, I willBrett 8-)PS Still, there a lot I don't know |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-03-07 : 16:21:51
|
quote:
Sometimes the natural key can't be used due to implementational constraints.
Sometimes IDENTITY can't be used due to model constraints... >4 billion rowsDavidM"SQL-3 is an abomination.." |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-07 : 16:27:21
|
| >> wouldn't you need an index to query the phone number as well.Nope. There is too much data comming in to do that. Any index other than that on the ID slows things down unnecessarily - and also gives more possibility of corruption.A rating engine just takes a call and generates an invoice entry.Anything which does an update or queries the call table will slow things down too much.You take a batch of calls (batch size tailored to the amount of records) and perform the rating functions on it. The result is an insert into the invoice entry table which will refernce the call.The rating will all be done on a copy of the batch of data in another table which will stay in memory.The only access to the call table is the insert process and one read per call to get the data.Any on-line queries are done on another server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 03/07/2003 16:29:03 |
|
|
|
|
Identity Columns.