December 3, 2003 at 2:15 pm
Hello all,
I am fairly new to SQL and databases, but i would like to create a stored procedure that will increment a counter by one whenever a new record is inserted. I want to stay away from the autoincrement feature. Also I need to follow a certain number configuration (ex. 02966, 02967)
December 3, 2003 at 2:38 pm
Why do you want to reinvent the wheel?
When you search this site, you'll find several threads, and I guess also scripts on your topic. However, I think, you should leave this to SQL Server.
As for your formatting, this can easily be done when presenting the data. It should not be necessary to store it in that format.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 4, 2003 at 2:11 am
When I started programming SQL Server stored procedures, I also did not use the autoincrement feature. So I created a stored procedure nextvalue which returned a new ID.
Everytime I wanted to insert a record I coded EXECUTE nextvalue @NewID OUTPUT. Using a SQL statement like INSERT INTO table_x SELECT col1,col2 FROM ... was impossible.
Took me some time to correct this situation with the IDENTITY columns.
Hope you will not make the same error.
Gerry S.
December 4, 2003 at 6:12 am
hi!
you should *avoid* something like a procedure/function which sort of selects a count and increments by one. why? think of it:
* everytime you insert a record, you'll have to query your database. espectially for large tables, this can lead to a *significant* degradation in performance, trust me, *no fun*
* you'll have to execute every single insert and it's accompanying procedure call to get the next id inside a transaction, otherwise you'll find yourself in serious troubles once you're working in an environment with real concurrency
all in all, i'd stick to franks opinion: don't try to reinvent the wheel 😉
best regards,
chris.
December 4, 2003 at 7:11 am
I agree with the other posters. Why should you reinvent this? Even in the case that you use stored procedures to enforce referential integrity rather than foreign keys, inherited a non-normalized database or non-cacading key architecture, it can easily be programmed to use the @@IDENTITY or @@SCOPE_IDENTITY variables to get your newly inserted "incremented" value. Plus as mentioned before you are just creating more overhead for the server by doing the extra work and all "presentation" stuff can be left to the calling application. This is a seperation of layers. A very good thing in programming databases.
Think great, be great!
December 4, 2003 at 7:22 am
I'd have to agree with folks on this one. I have done both. We still use a procedure in our Sybase database to get the next ID to use. It isn't as bad as Chris points out. We don't select MAX(ID)+1 from the table as we maintain a table that contains a table name and the next ID.
If you need your IDs formatted in a specific fashion then I would recommend you use the SQL Identity column for the primary key and the method for obtaining this. I'd then add a second Alternate Key column that is populated via an Insert trigger on the table. This way you don't have to format this ID when you want to view it. Something that would be next to impossible given all the entry points.
Identity columns good...
Jeff
December 4, 2003 at 9:57 am
Yeah, identities are the way to go, but realize that identities aren't reused, so if you add record 02966 and then delete it, you'll end up with a gap in your sequence.
This is an important consideration when using said number to track, say, an invoice number or some such.
I guess it depends what you intend to use this number for: as a "key" in the database sense of the word, or a user data field, like an invoice number. Mixing the two is a bad idea.
December 4, 2003 at 1:43 pm
Yes, identities can and almost always will have gaps, but that's what I meant with can easily be done when presenting the data, that is at the client
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 4, 2003 at 8:15 pm
WOW, thanks for all of your wonderful advice, I will not try to reinvent the wheel and give the identities a try. Now I know where to come to with issues. Once again thanks for the input.
Vince
December 5, 2003 at 5:45 am
Well done my friends! You've helped a reader solve a problem AND steered them into a better practice.
Andy
December 5, 2003 at 7:47 am
Well will ya look at that!
http://qa.sqlservercentral.com/columnists/glarsen/sequentialnumbering.asp
Ask and ye shall receive...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply