Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Re: SQL Server 2005 / 2008 Identity issue -nd-

Expand Messages
  • John Warner
    Yes, I agree with you and Paul, I think IDENT_CURRENT(mytable) is my best solution. I know the step rate so just adding 1 to the result will be good enough.
    Message 1 of 7 , Apr 30, 2009
    • 0 Attachment
      Yes, I agree with you and Paul, I think IDENT_CURRENT(mytable) is my
      'best' solution. I know the step rate so just adding 1 to the result will
      be good enough. The whole idea here is funky to me but this and some other
      oddities seems to be what is desired. I've explained that any use of the
      view I'll be providing will not always be up-to-date since users will
      could be inserting records right after I poll this and before the manager
      sees it. They seem willing to live with that reality.

      Thanks.

      John Warner


      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
      > Sent: Thursday, April 30, 2009 10:19 AM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: RE: [SQLQueriesNoCode] Re: SQL Server 2005 / 2008 Identity
      issue
      > -nd-
      >
      > Frankly speaking, there is no solution to your problem.
      > Either you go with IDENT_CURRENT (with no locking mechanism) solution or
      > adopt the TOKEN mechanism.
      >
      > Regards,
      > Noman Aftab
      >
      >
      > --- On Wed, 29/4/09, John Warner <john@...> wrote:
      >
      > From: John Warner <john@...>
      > Subject: RE: [SQLQueriesNoCode] Re: SQL Server 2005 / 2008 Identity
      issue
      > -nd-
      > To: SQLQueriesNoCode@yahoogroups.com
      > Date: Wednesday, 29 April, 2009, 5:08 PM
      >
      >
      >
      >
      >
      >
      >
      >
      > Not worried about duplication, just creating an Identity column and not
      > turning off that functionality guarantees that I don't get duplicates, I
      > am not intending to insert a value into the Identity column, I just want
      > to know what will SQL Server will gen up when someone does make the next
      > INSERT. Any Server function that returns the current value (that I can
      > query at anytime in relation to activity in the table) and tells me
      either
      > the current value or the next is fine. Please note I am not trying to
      > Insert a new record/row, I'm just asking so to speak for where the DB
      > stands when I poll it.
      >
      > Thanks for the suggestion.
      >
      > John Warner
      >
      > > -----Original Message-----
      > > From: SQLQueriesNoCode@ yahoogroups. com
      > > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Noman
      > Aftab
      > > Sent: Wednesday, April 29, 2009 6:50 AM
      > > To: SQLQueriesNoCode@ yahoogroups. com
      > > Subject: RE: [SQLQueriesNoCode] Re: SQL Server 2005 / 2008 Identity
      > issue
      > > -nd-
      > >
      > >
      > > CREATE TABLE [TOKEN](
      > > [TABLE_NAME] [varchar](255) NOT NULL,
      > > [NEXT_ID] [decimal](10, 0) NOT NULL)
      > >
      > > When I am required to show the number to user before it is inserted in
      > the
      > > database, I disable the identity on the primary key and control the
      > generation of
      > > IDs using a TOKEN mechanism.
      > >
      > > The TOKEN table contains the table name and the nextId that will be
      > inserted
      > > as a primary key.
      > >
      > > You click on "Add Record"
      > > the token table is queried for a particular table
      > > takes the NEXT_ID from it, and updates it with (+1)
      > >
      > > It guarantees that you will not encounter a duplication of primary key
      > issue.
      > >
      > > The only drawback is that you might end up missing values in between.
      > > for example, you open a form in Add mode (token is updated) but doesnt
      > > actually save the record.
      > >
      > > Regards,
      > > Noman Aftab
      > >
      > >
      > > --- On Tue, 28/4/09, John Warner <john@jwarner. com> wrote:
      > >
      > > From: John Warner <john@jwarner. com>
      > > Subject: RE: [SQLQueriesNoCode] Re: SQL Server 2005 / 2008 Identity
      > issue
      > > -nd-
      > > To: SQLQueriesNoCode@ yahoogroups. com
      > > Date: Tuesday, 28 April, 2009, 10:15 PM
      > >
      > >
      > >
      > >
      > >
      > >
      > >
      > >
      > > I'm not sure you ever quite wrapped your head around my 'silly'
      > > requirement/ desire. I am not planning to do an insert that would
      depend
      > on
      > > the value. Frankly if I insert the value that defeats an Identity
      > column's
      > > entire idea IMO. No, I just want and I think Paul has covered for me,
      a
      > > way to anticipate what the next value would be IF an insert were made.
      I
      > > understand in a multi user environment the value retrieved from
      > > IDENT_CURRENT is only good if no one inserts after I poll. A risk I'm
      > > willing to take I just want to have some idea what it would be. What
      I'm
      > > coding is a quick way to remotely (will be ASP.NET) check the status
      of
      > > several tables in a database that is across town. I'm not even
      concerned
      > > if SQL Server has crashed (well sort of but not my problem if it has).
      > The
      > > client just wants an idea how many rows are in each table which
      COUNT()
      > > does nicely and for some odd reason what the next insert will do to
      the
      > > PK. I have no idea why they want a look at the state between inserts
      but
      > > like the 600, mine but to do and die ;-)
      > >
      > > Thank you both for your help here. I am endlessly amazed at the
      quality
      > of
      > > help in this list.
      > >
      > > apologies to Tennyson.
      > >
      > > John Warner
      > >
      > > > -----Original Message-----
      > > > From: SQLQueriesNoCode@ yahoogroups. com
      > > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of Arnie
      > > Rowland
      > > > Sent: Tuesday, April 28, 2009 12:55 PM
      > > > To: SQLQueriesNoCode@ yahoogroups. com
      > > > Subject: RE: [SQLQueriesNoCode] Re: SQL Server 2005 / 2008 Identity
      > > issue
      > > > -nd-
      > > >
      > > > And of course, another row could have been added to the table WHILE
      > you
      > > > are doing that... Your newly incremented value would then be a
      > duplicate
      > > > of an existing value.
      > > >
      > > > My point previously is this: you cannot control IDENTITY sequencing
      in
      > a
      > > > multi-user concurrent environment. You 'should' not attempt to
      > > > anticipate the next IDENTITY values -attempting to do so will often
      be
      > > > futile.
      > > >
      > > > If you are using IDENTITY values, then use the OUTPUT clause or
      > > > SCOPE_IDENTITY( ) to obtain the value after the insert. That is the
      > only
      > > > guaranteed accurate method of obtaining the actual IDENTITY value
      > > > created from the INSERT.
      > > >
      > > > Without locking, you cannot control multi-user behavior. As
      indicated
      > in
      > > > the procedure locking paper I referred to earlier, async locking
      does
      > > > not scale, and usually will cause more issues than it solves. But it
      > can
      > > > be successfully employed in small scale situations.
      > > >
      > > > Regards,
      > > >
      > > > Arnie Rowland, MVP (SQL Server)
      > > >
      > > > "Fortune favors the prepared mind." Louis Pasteur
      > > >
      > > >
      > > >
      > > >
      > > > -----Original Message-----
      > > > From: SQLQueriesNoCode@ yahoogroups. com
      > > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of Paul
      > > Livengood
      > > > Sent: Tuesday, April 28, 2009 9:41 AM
      > > > To: Arnie
      > > > Subject: [SQLQueriesNoCode] Re: SQL Server 2005 / 2008 Identity
      issue
      > > >
      > > > Based on what I believe you need, I would use Ident_Current.
      > > >
      > > > Ident_Current is not scope/session dependent like @@Identity or
      > > > Scope_Identity. It will return the last value. To get the next value
      > > > you will need to add the step to it.
      > > > Here is BOLs descriptoin of the three functions/variables
      > > >
      > > > IDENT_CURRENT returns the last identity value generated for a
      specific
      > > > table in any session and any scope.
      > > >
      > > > @@IDENTITY returns the last identity value generated for any table
      in
      > > > the current session, across all scopes.
      > > >
      > > > SCOPE_IDENTITY returns the last identity value generated for any
      table
      > > > in the current session and the current scope.
      > > >
      > > >
      > > > So, if you want what was last inserted for a specific table
      > reguardless
      > > > of scope or session then use IDENT_CURRENT( Tablename) .
      > > >
      > > > If you need the next number you will need to add in the increment
      > value
      > > > Example
      > > > SELECT Ident_Current( 'TABLE_1' ) + IDENT_INCR(' TABLE_1')
      > > > This will give you the current value and add the increment.
      > > >
      > > > HTH
      > > > Paul
      > > >
      > > >
      > > > --- In SQLQueriesNoCode@ yahoogroups. com, "Arnie Rowland"
      > > <arnie@...>
      > > > wrote:
      > > > >
      > > > > John,
      > > > >
      > > > > It is always problematic attempting to anticipate IDENTITY values.
      > > > >
      > > > > IF your situation demands that you know in advance of an insert
      what
      > > > > 'next' value to use, I suggest that creating an async operation,
      > using
      > > > a
      > > > > numbers table, is the only viable solution. And it will not scale
      > very
      > > > > well.
      > > > >
      > > > > Here is one option: (url may wrap)
      > > > >
      > > > > Lock a Stored Procedure for Single Use Only
      > > > >
      > > > http://code. msdn.microsoft. com/SQLExamples/ Wiki/View. aspx?title=
      > > GetAppLo
      > > > > ck
      > > > >
      > > > >
      > > > > Regards,
      > > > >
      > > > > Arnie Rowland, MVP (SQL Server)
      > > > >
      > > > > "Fortune favors the prepared mind." Louis Pasteur
      > > > >
      > > > >
      > > > >
      > > > >
      > > > > -----Original Message-----
      > > > > From: SQLQueriesNoCode@ yahoogroups. com
      > > > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of John
      > > Warner
      > > > > Sent: Tuesday, April 28
      >
      >
      >
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.