Sign in
 

none
Aviod Identity Key column issues

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Read

    http://msdn.microsoft.com/en-us/library/aa933196%28v=sql.80%29.aspx

    Many Thanks & Best Regards, Hua Min

    Tuesday, July 16, 2013 6:13 AM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    select * from sys.identity_columns;

    Tom G.

    Tuesday, July 16, 2013 1:16 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    @@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
    |