Arithmetic overflow error converting IDENTITY to data type int - int to bigint questions

• krypto69

  SSChampion

  Points: 13604

  More actions

  September 17, 2017 at 10:56 am

  #328399

  Hi,

  I recieved this error:

  Arithmetic overflow error converting IDENTITY to data type int

  I believe I need to change my datatype from int to bigint on this table.

  This table has three contraints to three other tables, and of course a pk.

  Can I simply drop the pk - alter the datatype to bigint without effecting the other tables?

  Can I do this inplace? Do I need to reseed? The column is an identity on.

  I was thinking it might be best to create a copy of the table with all the contraints then export data from old table to new table (with bigint) . Is this even neccessary?

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  September 18, 2017 at 1:13 am

  #1959721

  So from that the INT column must be maxed out at 2147483647?

  If so then yes you will need to switch it to bigint, if not then you've got a different problem.

  Can you do the commands inplace, yes.

  Drop the constraints which rely on this PK, then alter the column, rebuild all the indexes to be on the safe side, change all the other tables which rely on the column, add back the constraints.  That will take some time to do for that many rows.

  CREATE TABLE #identityStatus   
(   
table_name        VARCHAR(128)   
    , column_name       VARCHAR(128)   
    , data_type         VARCHAR(128)   
    , last_value        BIGINT   
    , max_value         BIGINT   
);   
   
 INSERT INTO #identityStatus  
    SELECT    
  object_name(id.object_id) AS [table_name], 
        id.name AS [column_name],   
        t.name AS [data_type],   
        CAST(id.last_value AS BIGINT) AS [last_value],   
        CASE    
   WHEN t.name = 'TINYINT'   THEN 255
            WHEN t.name = 'SMALLINT'  THEN 32767    
            WHEN t.name = 'INT'      THEN 2147483647    
            WHEN t.name = 'BIGINT'    THEN 9223372036854775807   
   when t.name = 'KEY' THEN 32767
        END AS [max_value]   
    FROM    
  sys.identity_columns AS id 
    JOIN    
  sys.types AS t 
  ON 
  id.system_type_id = t.system_type_id 
    WHERE   
  id.last_value IS NOT NULL; 
   
SELECT    
 table_name,  
    column_name,   
    data_type,   
    last_value,   
 max_value,  
    CASE    
  WHEN last_value < 0 THEN 100 
        ELSE CONVERT(DECIMAL(18,5),((1 - CAST(last_value AS FLOAT(4)) / max_value) * 100))   
 END AS [PercentLeft],  
 CASE   
        WHEN CONVERT(DECIMAL(18,1),last_value) / max_value = 1   
   THEN 'Warning: Maximum limit reached'
        WHEN CONVERT(DECIMAL(18,1),last_value) / max_value >= 0.8   
            THEN 'Warning: approaching max limit'   
        ELSE 'Okay'   
 END AS [id_status],  
 GETDATE()  
FROM    
 #identityStatus  
ORDER BY    
 PercentLeft;  
    
drop table #identityStatus