Solved

Sql indentity

Posted on 2010-11-24
5
355 Views
Last Modified: 2012-05-10
hey guys, i need an sql query to find the indentity, the hard part is when the table is empty it returning  value of 2

select IDENT_CURRENT('Customer') + IDENT_INCR ('Customer')

i am looking for the next indentity, if the table is empty return 1 and if 1 record is inserted the sql statement must return 2 and so on
0
Comment
Question by:JCWEBHOST
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34204446
you should NOT try to "see" the next identity, at least not to display/use that.
only doing the insert, and then fetching the value using SCOPE_IDENTITY() is the correct (for multi.user apps anyhow) way to do
0
 

Author Comment

by:JCWEBHOST
ID: 34204507
give an exsample please
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34204567
Quote from books online: http://msdn.microsoft.com/en-us/library/ms175098.aspx
Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.

There is no other way I know of to correctly predict the next value.
The only way I know of that is not multi-user safe is to

insert a record, get scope_identity
delete record
fire ident_reseed to "return" the value used

This is, however, a terrible idea and as stated, not multi-user safe - you may end up reseeding wrongly.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34204804
after you run the INSERT (I presume you know how to do that), you run:

select scope_identity()

Open in new window

0
 

Author Closing Comment

by:JCWEBHOST
ID: 34228944
Thanks
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question