Need a more efficient way to get autonumber from SQL

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 1737 Views - Last Post: 28 June 2011 - 11:18 AM Rate Topic: -----

#1 lando786  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 29
  • Joined: 27-April 11

Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 10:04 AM

Hey guys,
Recently while developing several applications, I came across a problem where my current solution seems very error prone. I want to display the current autonumber seed in an SQL Server's DB Table. For instance, an autonumber column's last item is 746, I want the "Add Form" to display 747.

My current solution is to get the last entered item's number and add 1 to it like so:

SELECT Top 1 autonumber from table1 Order by autonumber DESC


That in return gives me the int 746. I then add 1 to make it 747 and display it on the form. However the problem is, if the item is deleted from the database then the query would return 745, but the DB seed will still be 746. Is there a way to query the autonumber seed directly to display that, instead of querying the value of the autonumber column?

My program is in C# but I'm not sure if this question would be better answered in the databases forum. Please forgive me if it was placed in the incorrect forum.

This post has been edited by lando786: 27 June 2011 - 10:05 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Need a more efficient way to get autonumber from SQL

#2 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 948
  • View blog
  • Posts: 2,357
  • Joined: 15-February 11

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 10:19 AM

Check out MAX. Doesn't look like you're using MySQL from the looks of your query. MS SQL?

This post has been edited by codeprada: 27 June 2011 - 10:20 AM

Was This Post Helpful? 0
  • +
  • -

#3 lando786  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 29
  • Joined: 27-April 11

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 10:21 AM

Yes sorry,it is MSSQL. Wow the things you forget to mention when the after-lunch drowsiness sets in.

Using the MAX function, wouldn't the problem still occur if the latest item is deleted? or does this MAX function return the value of the seed?

This post has been edited by lando786: 27 June 2011 - 10:23 AM

Was This Post Helpful? 0
  • +
  • -

#4 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 948
  • View blog
  • Posts: 2,357
  • Joined: 15-February 11

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 10:30 AM

MAX will return the highest value in that column. I think you may be confusing COUNT with MAX.

Let's say this is our table...
ID
1
3
5
7
8
9
10
13
14
16


This query
SELECT MAX(ID) FROM table
will return 16 while this one
SELECT COUNT(ID) FROM table
will return 10

I don't understand when you say 'the value of the seed'

This post has been edited by codeprada: 27 June 2011 - 10:30 AM

Was This Post Helpful? 0
  • +
  • -

#5 lando786  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 29
  • Joined: 27-April 11

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 10:43 AM

Okay say my table is this:

	ID
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10


Sequential since it is autonumber. What I want is to display "11" on the form. Using the method displayed in the original post would work as long as the value "10" would not be deleted in the database. I want to display "11" on the form even if all 10 values were to be deleted from the table.
Was This Post Helpful? 0
  • +
  • -

#6 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4577
  • View blog
  • Posts: 8,019
  • Joined: 08-June 10

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 10:43 AM

He wants to know what the next value is going to be. Auto numbers don't "fill gaps". If I start at one, insert three records, delete the last one, and insert another, I won't have a continuous sequence of auto numbers. I'd have { 1, 2, 4 }, since I deleted 3, then inserted.

Assume I'm still at the step immediately after I deleted record 3. If I query for MAX, I would get 2. 2 + 1 = 3. But in actuality, the next record will have 4, not 3. Therein lies the problem.

Now, I understand what you want, but it's simply not practical. Using an autonumber means that these numbers should not be significant until assigned. What business objective are you trying to achieve? It's likely that, since there is no easy way to do this, you might need to change your process.

Other than that, if you can maintain a state somehow on each insert, you can retrieve the autonumber and store it locally.
Was This Post Helpful? 1
  • +
  • -

#7 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9574
  • View blog
  • Posts: 36,261
  • Joined: 12-June 08

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 10:50 AM

I never understood people that had to keep their 'auto increments' free of gaps.

Other alternatives - utilization of the auto increment column info (though that will leave gaps); never delete a record only do a "soft delete" where you flip a boolean value. No gaps!
Was This Post Helpful? 1
  • +
  • -

#8 lando786  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 29
  • Joined: 27-April 11

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 10:51 AM

I want the number so I can print several labels. For example if autonumber is 10 the I can print the labels 10-1, 10-2, 10-3 and so on. However if the last number on the table is deleted then I find myself in the situation discussed in the thread.

View Postmodi123_1, on 27 June 2011 - 10:50 AM, said:

I never understood people that had to keep their 'auto increments' free of gaps.

Other alternatives - utilization of the auto increment column info (though that will leave gaps); never delete a record only do a "soft delete" where you flip a boolean value. No gaps!



Simple and obvious solutions are usually overlooked. Awesome suggestion, This just might work.
Was This Post Helpful? 0
  • +
  • -

#9 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4577
  • View blog
  • Posts: 8,019
  • Joined: 08-June 10

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 11:01 AM

In situations like this, I'd suggest changing your process. Autonumbers aren't significant until they're assigned. Trying to predict them is asking for trouble. If you're predicatively printing a label, then even if something is deleted here, you'll be printing a label for a deleted record.

My suggestion is to modify your process such that it doesn't rely on prediction, but reaction. It's possible (trivial, even) to write a query that will return the new autonumber after an insert. Print after the record is created, I'd suggest.

Otherwise, you shouldn't use the autonumber feature. If you absolutely need to predict numbers, you should use some other mechanism of generating IDs, one with controlled and defined behavior.
Was This Post Helpful? 1
  • +
  • -

#10 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1526
  • View blog
  • Posts: 5,961
  • Joined: 21-March 08

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 11:54 AM

Autonumbering should really only be used for identification of a record, which is why it's called an IDENTITY field.
Was This Post Helpful? 1
  • +
  • -

#11 T3hC13h  Icon User is offline

  • D.I.C Regular

Reputation: 65
  • View blog
  • Posts: 337
  • Joined: 05-February 08

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 06:36 PM

What your looking for is @@IDENTITY
Was This Post Helpful? 0
  • +
  • -

#12 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4577
  • View blog
  • Posts: 8,019
  • Joined: 08-June 10

Re: Need a more efficient way to get autonumber from SQL

Posted 27 June 2011 - 07:43 PM

Not really. First, @@Identity is flawed, in that it returns the last autonumber the DB has used. Scope_Identity is better, since it returns the last autonumber used in the scope. @@Identity is bad when you're dealing with lots of concurrent writes.

And second, that's really not the question. This was about predicting the next autonumber, not getting the last one used.
Was This Post Helpful? 1
  • +
  • -

#13 T3hC13h  Icon User is offline

  • D.I.C Regular

Reputation: 65
  • View blog
  • Posts: 337
  • Joined: 05-February 08

Re: Need a more efficient way to get autonumber from SQL

Posted 28 June 2011 - 07:08 AM

Saying @@IDENTITY is "Flawed" and "bad" seems a little strong. They each have their own purpose and have been well thought out by very smart people.

Back to the topic at hand. Without running an insert I wasnt able to get a return from @@IDENT or Scope_Identity, so yes, I was wrong, those wouldn't work to predict the next key.

I was however able to get the last ident value using
IDENT_CURRENT('tablename')
.

Also, I agree with Curtis that predicting the key may not be the best idea because, in my mind, unless your application is strictly single user, you can't guarantee that the item given the predicted key will actually get that on insert.
Was This Post Helpful? 1
  • +
  • -

#14 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4577
  • View blog
  • Posts: 8,019
  • Joined: 08-June 10

Re: Need a more efficient way to get autonumber from SQL

Posted 28 June 2011 - 07:48 AM

Quote

Saying @@IDENTITY is "Flawed" and "bad" seems a little strong. They each have their own purpose and have been well thought out by very smart people.


Well, I said those words with context, such as "bad when dealing with...". But @@IDENTITY is basically a relic, something from before they included Scope_Identity. I can't honestly think of a situation where you would actually want to use @@IDENTITY if you're already in a scope.
Was This Post Helpful? 0
  • +
  • -

#15 T3hC13h  Icon User is offline

  • D.I.C Regular

Reputation: 65
  • View blog
  • Posts: 337
  • Joined: 05-February 08

Re: Need a more efficient way to get autonumber from SQL

Posted 28 June 2011 - 11:00 AM

I looked up your claim of @@IDENTITY being a bad choice in a write heavy environment and, contrary to my understanding of your reasoning, it turns out that it only returns the last inserted value from within the context of the current connection. Any other inserts from other clients wont effect the value of @@IDENTITY of a given connection ( I confirmed this on SQLExpress 10. The only issue being that if your insert has side effects (from triggers) you may get the ident value from a different table/insert.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2