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

Re: [mugh-sqlcon] Identity column problem

Expand Messages
  • himanshu sinha
    Yes yuou are right but the question is why the enteries are not getting rolledback ?? Biswajit Das wrote: The main thing it is a case of
    Message 1 of 13 , Jan 11, 2005
      Yes  yuou are right
      but the question is why the enteries are not getting rolledback ??

      Biswajit Das <radhye@...> wrote:
      The main thing  it is a case of transaction

      so you can change the mode like this

      <Place the record into a temp table>

      commit

      when commit phase then transfer the data from temp to original table

       

      rollback

      if rollback then nothing happen.

      Note:once the identity column proceed on calculation it never back.so to remain the

      same starting number you have to operate in programatically manner

      Biswajit das

       

       

       

       

       

       

       

       

       

       



      himanshu sinha <kr_himanshu@...> wrote:
      Hi Biswajit  Das
       
      By this you ask the system to stop the calculation of the Identity value . But actually if we can find out how this value is calculated we can answer the query better .


      Biswajit Das <radhye@...> wrote:
      You can operate identity column by this commnad
       
       
      SET IDENTITY_INSERT products ON
      SET IDENTITY_INSERT products off
       
       
      thanks
       
       
       

       
       


      Biswajit Nanda <telljeet@...> wrote:

      Hi anybody has a solution to this problem?

      I have a table say X with two columns a and b where a
      is indentity and b is varchar(30).

      When I insert the first row it would be 1(if seed is
      1).
      Then I do one more insert. It would be 2.But after
      this i rollback the transaction.
      Then when I am inserting once again, it would be 3.

      Does anybody has an answer to this?

      Regards
      Biswajit




           
           
                 
      ___________________________________________________________
      ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com





      SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User Group.



      __________________________________________________
      Do You Yahoo!?
      Tired of spam? Yahoo! Mail has the best spam protection around
      http://mail.yahoo.com

      SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User Group.



      Do you Yahoo!?
      The all-new My Yahoo! � What will yours do?

      SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User Group.



      Do you Yahoo!?
      Yahoo! Mail - Easier than ever with enhanced search. Learn more.

      SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
      http://www.mugh.net/sql

      Visit http://www.mugh.net/dnug to join .NET User Group.



      Do you Yahoo!?
      The all-new My Yahoo! � What will yours do?

    • Rajkumar
      Hi, If u want to reset the identity value to restart from where it is, u can use the following dbcc checkident( tablename ,reseed,1) where 1 is the value u
      Message 2 of 13 , Jan 12, 2005
        Hi,
         
        If u want to reset the identity value to restart from where it is, u can use the following
         
        dbcc checkident('tablename',reseed,1)
         
        where 1 is the value u want the identity to be reseeded.
         
        Rgds,
        Raj

        himanshu sinha <kr_himanshu@...> wrote:
        Yes  yuou are right
        but the question is why the enteries are not getting rolledback ??

        Biswajit Das <radhye@...> wrote:
        The main thing  it is a case of transaction

        so you can change the mode like this

        <Place the record into a temp table>

        commit

        when commit phase then transfer the data from temp to original table

         

        rollback

        if rollback then nothing happen.

        Note:once the identity column proceed on calculation it never back.so to remain the

        same starting number you have to operate in programatically manner

        Biswajit das

         

         

         

         

         

         

         

         

         

         



        himanshu sinha <kr_himanshu@...> wrote:
        Hi Biswajit  Das
         
        By this you ask the system to stop the calculation of the Identity value . But actually if we can find out how this value is calculated we can answer the query better .


        Biswajit Das <radhye@...> wrote:
        You can operate identity column by this commnad
         
         
        SET IDENTITY_INSERT products ON
        SET IDENTITY_INSERT products off
         
         
        thanks
         
         
         

         
         


        Biswajit Nanda <telljeet@...> wrote:

        Hi anybody has a solution to this problem?

        I have a table say X with two columns a and b where a
        is indentity and b is varchar(30).

        When I insert the first row it would be 1(if seed is
        1).
        Then I do one more insert. It would be 2.But after
        this i rollback the transaction.
        Then when I am inserting once again, it would be 3.

        Does anybody has an answer to this?

        Regards
        Biswajit




             
             
                   
        ___________________________________________________________
        ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com





        SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User Group.



        __________________________________________________
        Do You Yahoo!?
        Tired of spam? Yahoo! Mail has the best spam protection around
        http://mail.yahoo.com

        SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User Group.



        Do you Yahoo!?
        The all-new My Yahoo! � What will yours do?

        SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User Group.



        Do you Yahoo!?
        Yahoo! Mail - Easier than ever with enhanced search. Learn more.

        SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User Group.



        Do you Yahoo!?
        The all-new My Yahoo! � What will yours do?

        SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
        http://www.mugh.net/sql

        Visit http://www.mugh.net/dnug to join .NET User Group.



        Do you Yahoo!?
        Yahoo! Mail - 250MB free storage. Do more. Manage less.

      • Vinod Kumar
        Hi Biswajit, Himanshu and others, I am returning to this group after a long time. Nevertheless its exciting to be with the SQL Buddies !!! I recollect this
        Message 3 of 13 , Jan 12, 2005
          Hi Biswajit, Himanshu and others,

          I am returning to this group after a long time. Nevertheless its
          exciting to be with the SQL Buddies !!!

          I recollect this question being asked from Veer sometime back to me.
          I understand its a pain point to skip a number as such (not sure
          what business requirement is driving you for this). If I were to put
          it politely and in a way of no use to you it would read "Its by
          design" :). I know that is the most frustrating answer that you can
          get. And the reality is the same ... I went through all the threads
          that lead to your question. Yes all of them have got the crux that
          we cannot do it in a straight forward way. We need to reseed or do
          an IDENITY_INSERT explicitly. Doing so has its own complications as
          we need to monitor the same. A real pain point.

          I might not be tell you exactly the depth of the architecture but
          want to demystify some of the comments. I read in one of the
          posts "Identity is stored in memory". This is not correct. This
          would be a dangerous too as you will loose the structure and actual
          values in event of a memory dump/service crash and nevertheless a
          server crash. Identity is managed as a physical *internal*
          structure. Moreover these structures DONOT participate in the
          logging operation of a normal T-SQL. I know you will bounce back
          asking "why?". Your scenario is valid and would feel can be easily
          implemented for a sequential single user mode. But think of the same
          in a multi-user scenario to how this extra bit of logging can bring
          more complexity to the identity value generation and assignment. Its
          a nightmare for the SQL Engine to digest so much for a single Insert
          operation (nor there is code to help you do that :)).

          Now the actual physical implementation is not documented or
          discussed in any forums. Only the SQL Dev teams have these internal
          info.

          I think I did try to address the "why" part of the question.
          But "how" part remains a mystery for the external world :) ...

          Hope this helps.

          Regards,
          Vinod Kumar
          MVP - SQL Server
          www.ExtremeExperts.com
          http://groups.msn.com/SQLBang/

          --- In sqlcon@yahoogroups.com, himanshu sinha <kr_himanshu@y...>
          wrote:
          > Yes yuou are right
          > but the question is why the enteries are not getting rolledback ??
          >
          > Biswajit Das <radhye@y...> wrote:
          > The main thing it is a case of transaction
          >
          > so you can change the mode like this
          >
          > <Place the record into a temp table>
          >
          > commit
          >
          > when commit phase then transfer the data from temp to original
          table
          >
          >
          >
          > rollback
          >
          > if rollback then nothing happen.
          >
          > Note:once the identity column proceed on calculation it never
          back.so to remain the
          >
          > same starting number you have to operate in programatically manner
          >
          > Biswajit das
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          > himanshu sinha <kr_himanshu@y...> wrote:
          > Hi Biswajit Das
          >
          > By this you ask the system to stop the calculation of the Identity
          value . But actually if we can find out how this value is calculated
          we can answer the query better .
          >
          >
          > Biswajit Das <radhye@y...> wrote:
          > You can operate identity column by this commnad
          >
          >
          > SET IDENTITY_INSERT products ON
          >
          > SET IDENTITY_INSERT products off
          >
          >
          > thanks
          >
          >
          >
          >
          >
          >
          >
          >
          > Biswajit Nanda <telljeet@y...> wrote:
          >
          > Hi anybody has a solution to this problem?
          >
          > I have a table say X with two columns a and b where a
          > is indentity and b is varchar(30).
          >
          > When I insert the first row it would be 1(if seed is
          > 1).
          > Then I do one more insert. It would be 2.But after
          > this i rollback the transaction.
          > Then when I am inserting once again, it would be 3.
          >
          > Does anybody has an answer to this?
          >
          > Regards
          > Biswajit
          >
          >
          >
          >
          >
          >
          >
          > ___________________________________________________________
          > ALL-NEW Yahoo! Messenger - all new features - even more fun!
          http://uk.messenger.yahoo.com
          >
          >
          >
          >
          >
          > SqlCon is a special interests group run under the aegis of MUGH -
          http://www.mugh.net
          > http://www.mugh.net/sql
          >
          > Visit http://www.mugh.net/dnug to join .NET User Group.
          >
          >
          >
          >
          > __________________________________________________
          > Do You Yahoo!?
          > Tired of spam? Yahoo! Mail has the best spam protection around
          > http://mail.yahoo.com
          >
          > SqlCon is a special interests group run under the aegis of MUGH -
          http://www.mugh.net
          > http://www.mugh.net/sql
          >
          > Visit http://www.mugh.net/dnug to join .NET User Group.
          >
          >
          >
          >
          >
          > ---------------------------------
          > Do you Yahoo!?
          > The all-new My Yahoo! – What will yours do?
          >
          > SqlCon is a special interests group run under the aegis of MUGH -
          http://www.mugh.net
          > http://www.mugh.net/sql
          >
          > Visit http://www.mugh.net/dnug to join .NET User Group.
          >
          >
          >
          >
          > ---------------------------------
          > Do you Yahoo!?
          > Yahoo! Mail - Easier than ever with enhanced search. Learn more.
          >
          > SqlCon is a special interests group run under the aegis of MUGH -
          http://www.mugh.net
          > http://www.mugh.net/sql
          >
          > Visit http://www.mugh.net/dnug to join .NET User Group.
          >
          >
          >
          >
          > ---------------------------------
          > Yahoo! Groups Links
          >
          > To visit your group on the web, go to:
          > http://groups.yahoo.com/group/sqlcon/
          >
          > To unsubscribe from this group, send an email to:
          > sqlcon-unsubscribe@yahoogroups.com
          >
          > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
          Service.
          >
          >
          >
          > ---------------------------------
          > Do you Yahoo!?
          > The all-new My Yahoo! – What will yours do?
        • Biswajit Nanda
          Hi Vinod, I also discovered the same after researching for a week almost.For my project we left it as it is. I also think if sequence of numbers is very
          Message 4 of 13 , Jan 12, 2005
            Hi Vinod,

            I also discovered the same after researching
            for a week almost.For my project we left it as it is.
            I also think if sequence of numbers is very important
            then we have to take care in the design by not going
            for identity column.The increment of numbers we can
            supplement by appropraite code in the procedure logic

            Thanks for the nice explanation.
            Regards
            Biswajit


            --- Vinod Kumar <waterworld_007@...> wrote:

            ---------------------------------


            Hi Biswajit, Himanshu and others,

            I am returning to this group after a long time.
            Nevertheless its
            exciting to be with the SQL Buddies !!!

            I recollect this question being asked from Veer
            sometime back to me.
            I understand its a pain point to skip a number as such
            (not sure
            what business requirement is driving you for this). If
            I were to put
            it politely and in a way of no use to you it would
            read "Its by
            design" :). I know that is the most frustrating answer
            that you can
            get. And the reality is the same ... I went through
            all the threads
            that lead to your question. Yes all of them have got
            the crux that
            we cannot do it in a straight forward way. We need to
            reseed or do
            an IDENITY_INSERT explicitly. Doing so has its own
            complications as
            we need to monitor the same. A real pain point.

            I might not be tell you exactly the depth of the
            architecture but
            want to demystify some of the comments. I read in one
            of the
            posts "Identity is stored in memory". This is not
            correct. This
            would be a dangerous too as you will loose the
            structure and actual
            values in event of a memory dump/service crash and
            nevertheless a
            server crash. Identity is managed as a physical
            *internal*
            structure. Moreover these structures DONOT participate
            in the
            logging operation of a normal T-SQL. I know you will
            bounce back
            asking "why?". Your scenario is valid and would feel
            can be easily
            implemented for a sequential single user mode. But
            think of the same
            in a multi-user scenario to how this extra bit of
            logging can bring
            more complexity to the identity value generation and
            assignment. Its
            a nightmare for the SQL Engine to digest so much for a
            single Insert
            operation (nor there is code to help you do that :)).

            Now the actual physical implementation is not
            documented or
            discussed in any forums. Only the SQL Dev teams have
            these internal
            info.

            I think I did try to address the "why" part of the
            question.
            But "how" part remains a mystery for the external
            world :) ...

            Hope this helps.

            Regards,
            Vinod Kumar
            MVP - SQL Server
            www.ExtremeExperts.com
            http://groups.msn.com/SQLBang/

            --- In sqlcon@yahoogroups.com, himanshu sinha
            <kr_himanshu@y...>
            wrote:
            > Yes yuou are right
            > but the question is why the enteries are not getting
            rolledback ??
            >
            > Biswajit Das <radhye@y...> wrote:
            > The main thing it is a case of transaction
            >
            > so you can change the mode like this
            >
            > <Place the record into a temp table>
            >
            > commit
            >
            > when commit phase then transfer the data from temp
            to original
            table
            >
            >
            >
            > rollback
            >
            > if rollback then nothing happen.
            >
            > Note:once the identity column proceed on calculation
            it never
            back.so to remain the
            >
            > same starting number you have to operate in
            programatically manner
            >
            > Biswajit das
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > himanshu sinha <kr_himanshu@y...> wrote:
            > Hi Biswajit Das
            >
            > By this you ask the system to stop the calculation
            of the Identity
            value . But actually if we can find out how this value
            is calculated
            we can answer the query better .
            >
            >
            > Biswajit Das <radhye@y...> wrote:
            > You can operate identity column by this commnad
            >
            >
            > SET IDENTITY_INSERT products ON
            >
            > SET IDENTITY_INSERT products off
            >
            >
            > thanks
            >
            >
            >
            >
            >
            >
            >
            >
            > Biswajit Nanda <telljeet@y...> wrote:
            >
            > Hi anybody has a solution to this problem?
            >
            > I have a table say X with two columns a and b where
            a
            > is indentity and b is varchar(30).
            >
            > When I insert the first row it would be 1(if seed is
            > 1).
            > Then I do one more insert. It would be 2.But after
            > this i rollback the transaction.
            > Then when I am inserting once again, it would be 3.
            >
            > Does anybody has an answer to this?
            >
            > Regards
            > Biswajit
            >
            >
            >
            >
            >
            >
            >
            >
            ___________________________________________________________

            > ALL-NEW Yahoo! Messenger - all new features - even
            more fun!
            http://uk.messenger.yahoo.com
            >
            >
            >
            >
            >
            > SqlCon is a special interests group run under the
            aegis of MUGH -
            http://www.mugh.net
            > http://www.mugh.net/sql
            >
            > Visit http://www.mugh.net/dnug to join .NET User
            Group.
            >
            >
            >
            >
            > __________________________________________________
            > Do You Yahoo!?
            > Tired of spam? Yahoo! Mail has the best spam
            protection around
            > http://mail.yahoo.com
            >
            > SqlCon is a special interests group run under the
            aegis of MUGH -
            http://www.mugh.net
            > http://www.mugh.net/sql
            >
            > Visit http://www.mugh.net/dnug to join .NET User
            Group.
            >
            >
            >
            >
            >
            > ---------------------------------
            > Do you Yahoo!?
            > The all-new My Yahoo! – What will yours do?
            >
            > SqlCon is a special interests group run under the
            aegis of MUGH -
            http://www.mugh.net
            > http://www.mugh.net/sql
            >
            > Visit http://www.mugh.net/dnug to join .NET User
            Group.
            >
            >
            >
            >
            > ---------------------------------
            > Do you Yahoo!?
            > Yahoo! Mail - Easier than ever with enhanced search.
            Learn more.
            >
            > SqlCon is a special interests group run under the
            aegis of MUGH -
            http://www.mugh.net
            > http://www.mugh.net/sql
            >
            > Visit http://www.mugh.net/dnug to join .NET User
            Group.
            >
            >
            >
            >
            > ---------------------------------
            > Yahoo! Groups Links
            >
            > To visit your group on the web, go to:
            > http://groups.yahoo.com/group/sqlcon/
            >
            > To unsubscribe from this group, send an email to:
            > sqlcon-unsubscribe@yahoogroups.com
            >
            > Your use of Yahoo! Groups is subject to the
            Yahoo! Terms of
            Service.
            >
            >
            >
            > ---------------------------------
            > Do you Yahoo!?
            > The all-new My Yahoo! – What will yours do?








            SqlCon is a special interests group run under the
            aegis of MUGH - http://www.mugh.net
            http://www.mugh.net/sql

            Visit http://www.mugh.net/dnug to join .NET User
            Group.



            Yahoo! Groups Sponsor ADVERTISEMENT


            ---------------------------------
            Yahoo! Groups Links

            To visit your group on the web, go to:
            http://groups.yahoo.com/group/sqlcon/

            To unsubscribe from this group, send an email to:
            sqlcon-unsubscribe@yahoogroups.com

            Your use of Yahoo! Groups is subject to the Yahoo!
            Terms of Service.






            ___________________________________________________________
            ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com
          Your message has been successfully submitted and would be delivered to recipients shortly.