finding the current ID value compared to the IDENTITY value

  • Hi

    I need to find the current identity value compared to the current column value of tables with identity fields - in particular where the current column value is less than the current identity value.

    I have run

    DBCC CHECKIDENT (tablename)

    which returns the required info and I can modify this code to use the MS_Foreachtable stored procedure.

    The problem is that I only really want to see output from this DBCC command where the current column value is less than the IDENTITY value.

    Is there a way of writing such a query that will give me only the tables where the current column value is less than the current identity value (displaying the identity value, and the current id value)

  • The only way I can think of is to loop through the tables and check each one

    CREATE TABLE #TableList (

    ID INT IDENTITY(1,1)

    , object_id INT PRIMARY KEY CLUSTERED

    , SchemaName SYSNAME

    , TableName SYSNAME

    , ColumnName SYSNAME

    , CurrentIdentity BIGINT

    , CurrentMaxValue BIGINT

    );

    INSERT INTO #TableList ( object_id, SchemaName, TableName, ColumnName, CurrentIdentity )

    SELECT

    ic.object_id

    , SchemaName = SCHEMA_NAME(t.schema_id)

    , TableName = t.name

    , ColumnName = ic.name

    , CurrentIdentity = CAST(ic.last_value AS BIGINT)

    FROM sys.identity_columns ic

    INNER JOIN sys.tables t ON ic.object_id = t.object_id

    WHERE t.type = 'U';

    DECLARE @ID INT = 0;

    DECLARE @sqlCmd VARCHAR(2000)

    SELECT @ID = MIN(ID) FROM #TableList WHERE ID > @ID;

    WHILE (@ID > 0)

    BEGIN

    SELECT @sqlCmd = 'UPDATE #TableList SET CurrentMaxValue = (SELECT CAST(MAX([' + ColumnName + ']) AS BIGINT) FROM [' + SchemaName + '].[' + TableName + ']);'

    FROM #TableList

    WHERE ID = @ID;

    PRINT @sqlCmd;

    EXEC (@sqlCmd);

    SELECT @ID = MIN(ID) FROM #TableList WHERE ID > @ID;

    END;

    SELECT *

    FROM #TableList

    WHERE CurrentIdentity <= CurrentMaxValue;

    DROP TABLE #TableList;

  • PearlJammer1 (10/14/2016)


    Hi

    I need to find the current identity value compared to the current column value of tables with identity fields - in particular where the current column value is less than the current identity value.

    Unless someone is doing something with negative increments... Since all values in the "current column" should, in fact, be less than the current identity value, I have to ask why you are trying to do this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff

    It is because we have a situation where some tables appear to have id values less than the current identity value which will get auto generated on next insert.

  • PearlJammer1 (10/17/2016)


    Hi Jeff

    It is because we have a situation where some tables appear to have id values less than the current identity value which will get auto generated on next insert.

    That is not an uncommon situation. If you do an insert and it gets rolled back, the IDENT that was generated is is not re-used, unless you reseed it with DBCC CHECKIDENT (tablename, RESEED [, new_reseed_value ])

  • PearlJammer1 (10/17/2016)


    Hi Jeff

    It is because we have a situation where some tables appear to have id values less than the current identity value which will get auto generated on next insert.

    So, just to be clear, you're saying that if you do an INSERT on a table that has an IDENTITY column, the value for that IDENTITY column for that newly inserted row appears as a number less than the maximum value already in that column?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (10/17/2016)


    PearlJammer1 (10/17/2016)


    Hi Jeff

    It is because we have a situation where some tables appear to have id values less than the current identity value which will get auto generated on next insert.

    So, just to be clear, you're saying that if you do an INSERT on a table that has an IDENTITY column, the value for that IDENTITY column for that newly inserted row appears as a number less than the maximum value already in that column?

    I have to admit I'm curious about this as well.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • What has happened (and I don't yet fully understand why but will discuss with our dev team) is that for example, we have an ID field on a table with IDENTITY(1,1). For arguments sake, the next IDENTITY returned from the query:

    SELECT IDENT_CURRENT('schema.mytable') AS CurrentIdentityVal,max(idcolumn) as CurrentMaxVal FROM schema.mytable

    Returns:

    CurrentIdentityVal = 110

    CurrentMaxVal = 100

    So I have been asked to produce a query that lists all the tables with their Current IDENTITY Value compared to the actual max value stored in the table.

    Does this clear things up ?

  • Thanks for the input on this Des

    I have modified the query as the I was getting NULL in all of the CurrentMaxValue columns

    I came up with this

    Create table #MyTable

    (

    ID INT IDENTITY(1,1),

    [object_id] int,

    SchemaName varchar(75),

    TableName varchar(75),

    ColumnName varchar(75),

    last_value int,

    CurrentMaxValue BIGINT

    )

    CREATE Table #Results

    (

    [object_id] int,

    SchemaName varchar(75),

    TableName varchar(75),

    ColumnName varchar(75),

    last_value int,

    CurrentMaxValue BIGINT

    )

    DECLARE @ID INT = 0;

    DECLARE @sqlCmd VARCHAR(2000)

    INSERT INTO #MyTable ([object_id],SchemaName,TableName,ColumnName,last_value)

    select st.object_id,SchemaName = SCHEMA_NAME(st.schema_id), st.name as TableName, ic.name as ColumnName, last_value = CAST(ic.last_value AS BIGINT)

    from sys.tables st

    INNER JOIN sys.identity_columns ic

    on st.object_id = ic.object_id

    where ic.is_identity = 1

    DECLARE @SQL VARCHAR(MAX)

    SELECT @ID = MIN(ID) FROM #MyTable WHERE ID > @ID;

    WHILE @ID>0

    BEGIN

    SElect @sql = 'UPDATE #MyTable SET CurrentMaxValue = (SELECT CAST(MAX([' + ColumnName + ']) AS BIGINT) FROM [' + SchemaName + '].[' + TableName + ']);'

    from #MyTable

    WHERE ID=@ID

    exec (@SQL)

    insert into #Results

    select top 1 [object_id],SchemaName,TableName,ColumnName,last_value,CurrentMaxValue

    FROM #MyTable

    delete TOP (1) from #MyTable

    SELECT @ID = MIN(ID) FROM #MyTable WHERE ID > @ID

    END

    SELECT * FROM #Results

    where CurrentMaxValue <> last_value

    drop table #MyTable

    DROP TABLE #Results

  • PearlJammer1 (10/17/2016)


    What has happened (and I don't yet fully understand why but will discuss with our dev team) is that for example, we have an ID field on a table with IDENTITY(1,1). For arguments sake, the next IDENTITY returned from the query:

    SELECT IDENT_CURRENT('schema.mytable') AS CurrentIdentityVal,max(idcolumn) as CurrentMaxVal FROM schema.mytable

    Returns:

    CurrentIdentityVal = 110

    CurrentMaxVal = 100

    So I have been asked to produce a query that lists all the tables with their Current IDENTITY Value compared to the actual max value stored in the table.

    Does this clear things up ?

    There's nothing wrong with that particular scenario. It just means 1 of several things.

    1. Rows were deleted from the table.

    2. One of more inserts failed and were rolled back, which still "consumes" an IDENTITY value.

    3. The SQL Server Service was restarted, which sometimes causes IDENTITY values to be lost because of the supposed pre-optimization SQL Server does by creating new IDENTITY values behind the scenes ahead of time. Such pre-created IDENTITY values are lost "forever".

    Now, if things were turned around like this...

    CurrentIdentityVal = 100

    CurrentMaxVal = 110

    ... THEN you'd have a real problem on your hands. That would mean that someone has been messing around with the identity seed for the table or someone has been using SET IDENTITY INSERT ON.

    IDENTITY values are not guaranteed to be sequential in an IDENTITY column for the reasons I just stated.

    For what you have (and thank you VERY much for clarifying with code), there is no non normal problem. I personally think it's a total waste of time to go through the drill you've been asked to go through unless you have a known issue with missing data.

    If they insist, the following code will find the anomalies you've mentioned. It could take quite a while to execute. If it returns nothing, then no such anomalies exist. If you want proof, add two dashes in front of the word HAVING in the dynamic SQL to return the values for ALL identity columns found in the database. Either way, you should charge them $100 USD and send me half. 😉

    /**********************************************************************************************************************

    Purpose:

    For the current database, find all tables having an IDENTITY column, compare the value of IDENT_CURRENT against the

    MAX value in the column, and report those tables and values that aren't an exact match.

    Revision History:

    Rev 00 - 17 Oct 2016 - Jeff Moden

    Initial creation and unit test.

    REF: http://www.sqlservercentral.com/Forums/Topic1826014-3411-1.aspx

    Rev 01 - 18 Oct 2016 - Jeff Moden

    Add QUOTENAME() to the REPLACEs to cover the eventuality of names with dashes, spaces, and other horrors. ;-)

    **********************************************************************************************************************/

    --===== Declare the code accumulator variable

    DECLARE @SQL VARCHAR(MAX)

    ;

    --===== Find every IDENTITY column and build the dynamic SQL to list the

    -- IDENT_CURRENT value and the MAX value in the column.

    -- This uses a <<TOKEN>> replacement method instead of a bazillion quotes and plus signs.

    SELECT @SQL = ISNULL(@SQL + ' UNION ALL','')

    + REPLACE(REPLACE(REPLACE(REPLACE('

    SELECT QualifiedObjectName = "<<SchemaName>>.<<ObjectName>>"

    ,CurrentIdentityVal = IDENT_CURRENT("<<SchemaName>>.<<ObjectName>>")

    ,CurrentMaxVal = MAX(<<ColumnName>>)

    FROM <<SchemaName>>.<<ObjectName>>

    HAVING IDENT_CURRENT("<<SchemaName>>.<<ObjectName>>") <> ISNULL(MAX(<<ColumnName>>),-2147483648)

    ' -- These are the other end of the REPLACEs.

    ,'"','''')

    ,'<<SchemaName>>',QUOTENAME(OBJECT_SCHEMA_NAME(object_id))) --Rev 01

    ,'<<ObjectName>>',QUOTENAME(OBJECT_NAME(object_id))) --Rev 01

    ,'<<ColumnName>>',QUOTENAME(name)) --Rev 01

    FROM sys.columns

    WHERE is_identity = 1

    AND OBJECT_SCHEMA_NAME(object_id) <> 'sys'

    AND OBJECTPROPERTY(object_id,'IsTable') = 1

    ;

    --===== Display ALL the code to be executed up to the length limit of the server XML max length.

    -- This returns a clickable XML "Cell" when in the GRID MODE for result sets in SSMS.

    SELECT SQLCode = (SELECT REPLACE(CAST('--' + CHAR(10) + @SQL + CHAR(10) AS VARCHAR(MAX)), CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.

    AS [processing-instruction(SQLCode)]

    FOR XML PATH(''), TYPE)

    ;

    --===== Uncomment this once you've determined that the dynamic SQL is actually safe to run.

    --EXEC (@SQL)

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff

    As ever, your thoughts are greatly appreciated so thanks for your input on this. I have posted the script that I finally used to return what i am after further up in the post - thanks to Des for his input on that as well.

    I'll keep your script safe and run it on my laptop against the Adventureworks database to get a feel for how it works. I didn't know that about the identity values being lost sometimes if you restart sql server - that sounds like a worrying bug to me !

    As you say the real problem would be if the values returned where the other way around - thankfully they are not.

    Much appreciated

  • PearlJammer1 (10/18/2016)


    Hi Jeff

    As ever, your thoughts are greatly appreciated so thanks for your input on this. I have posted the script that I finally used to return what i am after further up in the post - thanks to Des for his input on that as well.

    I'll keep your script safe and run it on my laptop against the Adventureworks database to get a feel for how it works. I didn't know that about the identity values being lost sometimes if you restart sql server - that sounds like a worrying bug to me !

    As you say the real problem would be if the values returned where the other way around - thankfully they are not.

    Much appreciated

    First, thank you for your always-kind feedback. Much appreciated.

    I made a small change to the code to cover the eventuality of a naming problem where the schema name, object name, or column name might be malformed (has a dash or space in it or starts with a $, etc, etc) or a reserved word. I've updated the code above. Search for "Rev 01" in the code to see that I only added QUOTENAME() to the operands in the REPLACE functions.

    Also, I ran this on two of my databases on my production box. Both of the databases have more than 1,300 tables each, some without IDENTITY columns. It never took more than about 12 seconds to execute and that's even while a backup is executing on the database the code was running against.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply