SQL Server Performance

problem with @@IDENTITY?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sekharabcs, Oct 7, 2009.

  1. sekharabcs New Member

    Hi All,
    I have a strange problem. We have a stored procedure SP1 which is inserting data into a table PD. This procedure is inserting data by joining 2 tables and also it is inserting two variables which are populated in this sp. They are ID and Date fields
    ID = returned by another SP2. Identity value is returned by this SP based on getdate()
    Date = getdate()
    Now, if I see the data in table PD. I am able to see that the highest value of ID is 68054. However, the strange thing is I am able to see many records with ID: 999999.
    I am unable to find out why the ID value is 999999. This ID column is an Identity column and a PK in another table.
    Please help me with this issue. Please let me know if I am not clear with my question.
    Procedure Code:
    Create Procedure SP1
    (@SomeId Varchar(Max) = Null, @OtherId VarChar(Max) = Null)
    As Declare @ID Int, @Date DateTime
    Set NoCount On

    Exec @ID = SP2 -- ID is returned from this SP2.
    Select @Date = Created From Despatch.PickList Where Id = @PickList
    If @SomeID Is Not Null
    Begin
    Delete From PD Where SomeID In (Select Value From dbo.ufn_Split(@SomeId))
    Insert Into PD
    ( Id, Created, SomeID )
    Select @ID, @Date, LD.Id
    From LD
    Join LH On LD.LHId = LH.Id
    Join LI On LD.OLId = LI.Id
    Where LD.Id In (Select Value From dbo.ufn_Split(@SomeId))

    Update LD
    Set PickDate = @Date, PickNumber = @ID
    Where Id In (Select Value From dbo.ufn_Split(@SomeID))
    End

    If @OtherId Is Not Null
    Begin
    Delete From PD Where LIId In (Select Value From dbo.ufn_Split(@OtherId))
    Insert Into PD
    (IDId, Created, SomeID)
    Select Distinct @ID, @Date, Null
    From OL
    Left Join OH On OL.OrderId = OH.Id
    Left Join LD On OL.Id = LD.OLId
    Left Join LH On LD.LHId = LH.Id
    Where OL.Id In (Select Value From dbo.ufn_Split(@OtherId))
    End

    Update OL
    Set LS = 50900
    Where Id In
    (Select LIId
    From PD
    Where ID = @ID
    )

    Select @ID

    Set NoCount Off

    I am assuming that there is some problem with Identity. SP2 is returning @@IDENTITY which @ID in above SP. Just wondered why @ID is 999999 for some records.
  2. FrankKalis Moderator

    By any chance, is there another table with an identity column involved? Perhaps by a trigger? I would suspect the problem is @@IDENTITY as well. It really should be SCOPE_IDENTITY() that is returned. Tehre is a good explanation on the differences between these two in BOL. I think it's explained when you look for "@@IDENTITY".
  3. sekharabcs New Member

    Thank you for the reply Frank.
    I could see that there is no trigger on the tables used by the above SP. However, there are some other tables with Identity column involved in the same schema but i dont think these are related, also no triggers on these tables too.
    Any other ideas?
    Thanks
    Sekhar
  4. Adriaan New Member

    In your PD table, the ID column is not an identity column, otherwise it could not have duplicate values.
    It would be more interesting to see what the code in SP2 looks like, since that's where the erratic values appear to be coming from.
  5. sekharabcs New Member

    My Apologies Adriaan, ID columns in not an identity. Its a INT which allows NULLS.
    SP2 code:
    Insert Into table2 (Created)
    Values (GetDate())
    Return @@Identity
    The identity is returned to @ID in SP1, which is sometimes returning 999999 I feel. PD table data is getting populated by the SP1 only. So the reason I am thinking IDENTTITY is causing 999999 to insert into PD table.
    Reuqest your help in this regard.
    THanks
    Sekhar
  6. Adriaan New Member

    Check the data type for the identity column on this table2 - it can be different from INT.
    Presuming SP2 has an output parameter, what's the data type of that?
    If it doesn't have an output parameter, I suggest you add one, and change the call in SP1 accordingly.
  7. sekharabcs New Member

    datatype for identity in table2 is INT
    SP2 is not having any output parameter. I will create a one and check.
    1. So do you mean that @@IDENTITY when it is returned from SP2 to SP1 is having some problem and that can be corrected by adding an output paramter to SP2?
    2. Any idea in which case @@IDENTITY will return 999999.
    Thanks,
    Sekhar
  8. Adriaan New Member

    Without the output parameter, SQL may be doing some implicit data conversion. Always safer to declare things, rather than to "rely" on implicit conversions.

Share This Page