none
Aviod Identity Key column issues

    Question

  • Hi experts,

    I want to develop mechanism which will give analysis of identity columns and fields whihc about to reach max, say tolernace of .1K records, then it would be enough around for user to check this pro-actively.

    is there standard mechanism in SQL server 2008 whihc will tell about IDENTITY columsn.


    Shivraj Patil.

    Tuesday, July 16, 2013 5:48 AM

Answers

  • Hi Shivraj,

    We can use @@IDENTITY to get the current value of the identity column, and then compare the current value with its max value. I made an example, you can refer to it:

    DECLARE @IDENT table
    (
    	ID int identity (1,1),
    	Name varchar(10)
    )
    
    insert into @IDENT  values ('a');
    
    SELECT @@IDENTITY;
    
    select 2147483647-@@IDENTITY as NumLeft;
    


    Allen Li
    TechNet Community Support

    Wednesday, July 17, 2013 6:29 AM

All replies

  • Tuesday, July 16, 2013 6:13 AM
  • You can use the below script to get the details of identity column for the tables in the database. From the last value and datatype size you can calculate the column that will reach the max based on the tolerance...try this.

    SELECT TableName = OBJECT_NAME(ic.OBJECT_ID) ,
           ColumnName = c.name ,
           OriginalSeed = seed_value ,
           Step = increment_value ,
           LastValue = last_value,
    	   DataType = t.name,
    	   SizeInBytes = t.max_length,
    	   t.precision,
    	   t.scale
    FROM 
    	sys.identity_columns ic
    INNER JOIN sys.columns c
    	ON c.name = ic.name
    		AND c.object_id = ic.OBJECT_ID
    INNER JOIN sys.types t
    	ON t.system_type_id = c.system_type_id
    WHERE
    	c.is_identity = 1
    


    Krishnakumar S

    Tuesday, July 16, 2013 6:20 AM
  • select * from sys.identity_columns;

    Tom G.

    Tuesday, July 16, 2013 1:16 PM
  • Hi Shivraj,

    We can use @@IDENTITY to get the current value of the identity column, and then compare the current value with its max value. I made an example, you can refer to it:

    DECLARE @IDENT table
    (
    	ID int identity (1,1),
    	Name varchar(10)
    )
    
    insert into @IDENT  values ('a');
    
    SELECT @@IDENTITY;
    
    select 2147483647-@@IDENTITY as NumLeft;
    


    Allen Li
    TechNet Community Support

    Wednesday, July 17, 2013 6:29 AM
  • @@IDENTITY may not return your value you should be using SCOPE_IDENTITY() to make sure the value you get belongs to your most recent insert.

    Tom G.

    Wednesday, July 17, 2013 11:24 AM