Query to get first number
-
quote:
When the identity reaches the maximum for an int. It will stop accepting records (overflow error).The only way around this is using a GUID column, but that adds some overhead (and storage too).
You can change your datatype doing an alter table - identities can be used on numeric(99,0) if you feel that you'll ever need that many.
-
Frank,
Just read your post (well read it before, but didn't get it... Few glasses of wine and a bunch of Microsoft sales people work wonders
). Nice solution though.I was already thinking about storing the 'next' ID in a table somewhere and keeping it up to date using triggers.
Maybe a trigger on delete to get the lowest number isn't that bad from a performance point of view.
By the way, if at all possible, I would use an IDENTITY. But hey, Business people request the stupidest things from time to time
-
quote:
Few glasses of wine and a bunch of Microsoft sales people work wonders).
ah, yes, those famous marketing events. Life can be hard sometimes
Especially on the next morningquote:
I was already thinking about storing the 'next' ID in a table somewhere and keeping it up to date using triggers.Maybe a trigger on delete to get the lowest number isn't that bad from a performance point of view.
By the way, if at all possible, I would use an IDENTITY. But hey, Business people request the stupidest things from time to time
I do like IDENTITY, too. But no guarantee to be really ongoing. So I decided for myself to forget about this and use consecutive numbering only when presenting data.
Another alternative depends on which tool you use to present the data. AFAIK, at least the Access Report Generator, has a VERY easy way to implement consecutive numbering on an sorted result set. Works very nice and I think other programms can do the same trick. So I really see no need to put that much brainwork into some logic when it isn't neccessary at all.
And yes, I know, Business people are sometimes beyond good and evil.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You'll always (never say never
) have to interpret business "demands" to "needs".You can do the same thing in # ways. Some are already constructed for you, maybe you'll have to adapt a bit. Evaluate pros to cons and choose.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Some interesting stuff...
Seems to me, though, that those arguing in favor of using an IDENTITY property are missing the point. Sure, you could use an IDENTITY -- if you don't mind gaps. But the 'spec' states, indirectly at least, that there are to be no gaps.
What they're really arguing for is a difference spec, not a solution for the existing spec.
There have been occasions when a user wanted me to set up a sequence number. I always ask, "Are gaps okay?" Then the user looks thoughtful and says, "No, I don't want gaps." His requirement may be completely bogus, but how much patience should anyone have with arguing with a user? Save the arguments for important stuff.
Edited by - Lee Dise on 09/05/2003 1:20:04 PM
). Nice solution though.
) have to interpret business "demands" to "needs".Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply