How to prevent exhausted Indentity
-
Hi All,
I'm a fan of identity data type, I always use identity as a primary key in all my tables. However, I'm now designing a heavy transactional database and deletion of data is not allowed. I predict it will run out of value within 7-8 years, tough I've set up my identity as a bigint type.
I'd like to have suggestions from all of you who may have experience dealing with such a situation.
I appreciate any comment and Thanks in advance.
Hendry
-
One option might be to make it a composite key, add a one/two char column that would be a set to a default ("A"), combine it with your ident column. Add a job that checks the ident daily, if within x of hitting the max, change the default to "B".
The other option is a uniqueidentifier. Not as fast as int/bigint, but you dont have to worry about it either.
Andy
-
Well, I doubt you'll run out of values if you do something like this:
CREATE TABLE Biggie(
Id dec(38,0) IDENTITY(-99999999999999999999999999999999999999,1))--Jonathan
--Jonathan -
At that point you're at 17 bytes per ID compared to 16 for a GUID. Good alternative though. And I suppose that you could still possibly exhaust the range someday...!
Andy
-
quote:
At that point you're at 17 bytes per ID compared to 16 for a GUID. Good alternative though. And I suppose that you could still possibly exhaust the range someday...!Andy
Hmmm; if he seeds his bigint starting at -2^63 and exhausts the values after 7 years, the the dec(38,0) will last about 1.4E+38 years. My example was obviously the extreme; dec(28,0) uses 13 bytes and should last Hendry until well after the sun burns out.
--Jonathan
--Jonathan -
I hear you! (Unless the rate of usage increases...)
Andy
-
Andy and Jonathan, Thanks for the suggestion. Both is really worthy for me. However, I have one more question. Why the uniqueidentifier is slower than other data type? Is that because of its big byte size?
Hendry
-
Hi,
from BOL
quote:
The main advantage of the uniqueidentifier data type is that the values generated by the Transact-SQL NEWID function or the application GUID functions are guaranteed to be unique throughout the world.The uniqueidentifier data type has several disadvantages:
The values are long and obscure. This makes them difficult for users to type correctly, and more difficult for users to remember.
The values are random and cannot accept any patterns that may make them more meaningful to users.
There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on incrementing key values serially.
At 16 bytes, the uniqueidentifier data type is relatively large compared to other data types such as 4-byte integers. This means indexes built using uniqueidentifier keys may be relatively slower than implementing the indexes using an int key.
Consider using the IDENTITY property when global uniqueness is not necessary, or when having a serially incrementing key is desirable.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for that!
Hendry
-
quote:
One option might be to make it a composite key, add a one/two char column that would be a set to a default ("A"), combine it with your ident column. Add a job that checks the ident daily, if within x of hitting the max, change the default to "B".
How about using the year(2 or 4 digits) as part of the composite key? May need a sober scheduled job on 31 Dec 23:59:59.996 to reset the indentity to -9999999999??
-
quote:
well after the sun burns out.
taking these for posting some broker humor.
Something we were short recently.
Police used tear gas and batons to break up a mob of angry unemployed
stockbrokers in the heart of the financial district as the FTSE100 slumped
through the 500 mark to finish at 497.2. The brokers were demonstrating
outside of the Stock Exchange building, demanding an audience with the
recently elected chairman, James Fleming. When he failed to appear, the
brokers began attacking the building and security staff with briefcases and
what appeared to be rolled up social security forms.
With unemployment in the financial services industry hovering at nearly
90%, the Government has ordered an inquiry into whether it is
feasible to permanently retrain the growing army of brokers and other
fallouts from the financial services industry. "It is very difficult
though," said a spokesman. "It does not appear that they have any useful
skills * legal ones anyway - which may be redirected to more productive
pursuits."
Brokers have become increasingly desperate as the equity market continues
to
slide and the war in Iraq enters its ninth month with little sign that US
forces are making any progress. There was a brief 5-point rally in the
market yesterday on news that Saddam Hussein had been captured, but it
turned out to be another "look-alike." "We have now detained more than 300
men and 2 women who bear a striking resemblance to the Iraqi dictator,"
Colonel T.J. Muskrat of the 98th Rangers told a press briefing in Baghdad.
The oil price continues to hover at $US60/barrel as motorists began to
adjust to the second week of petrol rationing. Commuters have also praised
the introduction of rat-powered treadmills to tube trains. Meanwhile, many
online employment web sites were inundated yesterday on news that Merrill
Morgan Suisse Warburg Barney, one of the three remaining brokerages, was
planning to advertise for a receptionist's assistant. Bill Pettigrew at
Seekjob.com said brokers swamped his site and forced it off line for an
hour. MMSWB later denied the rumour, and said they intended to continue
with
their recently announced program of staff cuts.
Anthony Pope, a former client adviser at ABNAmroMorgans, said the news
"perked him up even though I knew it couldn't be true." Yesterday's
tentative market rally soon petered out and the market closed near its
lows.
An LSE spokesman said the reduced trading hours (10.00-10.30am) appeared to
be working well. The Nikkei descended below 100 for the second time in a
fortnight, and the Bank of Japan was again the main buyer of stocks. It
issued another 725 trillion yen of government bonds, with a coupon of
0.00003% per annum and maturing when the sun finally sets on the Japanese
empire.Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Just a thought - why not use bigint?
It holds -2^63 through 2^63-1 and storage size is ony 8 bytes?
Jeremy
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply