locked
SQL Server identity column RRS feed

  • Question

  • User-1525009598 posted

    Hello all -

    Sort of an odd question here.

    I'm thinking of doing an update to the batch application I support which would alter a Linq to SQL repository update to change to a regular SQL Server stored procedure update.  it would be a regular update statement which created a new value in the identity value for the key.  Then there is another repository update to gather that new identity value and place it in a separate string column and do another repository update to update that column with the new identity value just generated. 

    My question is this.  Is there a way to gather that new identity value JUST created and update the other column with that value in the same stored procedure?  It's rather cumbersome.  The current code updates through the repository update, gathers the new identity column value just created, then does another repository save to update that other string column.  Is there a more efficient way todo  the update within the stored procedure to perform this scenario?

    In the larger sense, I've been assigned the unenviable task of trying to get this batch process application to make it run considerably faster to accommodate larger batches of data.  This means looking at operations that may take .3 or .4 seconds, time frames perfectly acceptable in the past, and try to make them better.  The process currently uses Linq to SQL repository updates.  I'm looking to try to convert updates such as the one I just described to a stored procedure update, in an attempt to cut down on that .3 to .4 second time frame.  Any ideas on whether my effort may be in vain, as I suspect it may be, as performance improvement through stored procs may not be any better than repository updates?  Any suggestions would be welcome, to avoid going too far down the rabbit hole...

       

    Monday, August 22, 2016 3:19 PM

All replies

  • User77042963 posted

    Can you mark up an example table and procedure? We can try to find a query that may work  to fit your need.

    Monday, August 22, 2016 3:23 PM
  • User753101303 posted

    Hi,

    My understanding is that you have to copy the identity value to another column when a row is created. I would consider:
    - using an insert trigger to keep this server side
    - if not needed right now, it could be done later using a single statement (for example if this copy is required for a later db side job)
    - if never changed it could be a computed column
    - ultimately reviewing the reason for this column to see if it is really needed

    Thursday, August 25, 2016 9:46 AM
  • User-1404113929 posted

    hi,

    @@Identity 


    can you call after insert query it returns current record identity column value.

    Wednesday, August 31, 2016 9:38 AM