Can save more if maximum limit value for Integer data type in SQL Server?

Question

I have a table with an Id field type int in SQL Server. 10 years later, can save more if Id reached the maximum value is 2,147,483,647? How to resolve if the table can not be saved anymore?

You can alter the type of column to bigint, as and when you get the error. You might even create a trigger to notify relevant DBA when it is close to full. — Nisarg Shah
– Nisarg Shah, Commented Mar 8, 2018 at 7:43
If the column is a primary key, you can't allow duplicate values into it. — Nisarg Shah
– Nisarg Shah, Commented Mar 8, 2018 at 7:51
It's also worth noting that there's no reason to start an ID column at 1. The Id column, at the end of the day, is just a ID it's not's graphically displayed; thus you could easily set it to start at -2147483648, giving you double the numbers you had before. if you've already initialised the IDENTITY this doesn't help, but it's worth keeping in mind. — Thom A
– Thom A ♦, Commented Mar 8, 2018 at 7:58

Mureinik · Accepted Answer · 2018-03-08 07:49:46Z

2

You could always modify the column type. Note that you can't do this while the column is part of the primary key, so you'd have to drop the constraint and then re-create it:

ALTER TABLE mytable DROP CONSTRAINT mytable_pk;
ALTER TABLE mytable ALTER COLUMN id BIGINT NOT NULL;
ALTER TABLE mytable ADD CONSTRAINT mytable_pk PRIMARY KEY (id);

answered Mar 8, 2018 at 7:49

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bhanu Prakash · Accepted Answer · 2018-03-08 07:51:32Z

0

You can alter the table and change the column data type to bigint anytime, but it is much recommended to have a trigger which will notify the DBA. Just in case if it's a primary key column, you have to delete the constraint, alter the column, change the data type and then add the primary key constraint again. Same would be the case if the column is a foreign key to some other table.

answered Mar 8, 2018 at 7:51

Bhanu Prakash

511 silver badge12 bronze badges

Collectives™ on Stack Overflow

Can save more if maximum limit value for Integer data type in SQL Server?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related