CODEWITHSUNDEEP
sqlsql-server
DeveloperMS
DeveloperMS

Reputation: 1

Setting max limit for Identity columns in SQL Server

I wanted to know if we can set a maximum limit for an identity column? Like in DB2, we can achieve this by using MAX clause of identity column.

Something like :

create table test
(
    id int generated always as identity(start with 1, increment by 1, max 100),         
    name varchar(50)
)

Same way can we set maximum limit explicitly for identity column in SQL Server?

Thanks in advance

Upvotes: 0

Views: 2177

Answers (1)

Pedro Augusto Freitas de Souza
Pedro Augusto Freitas de Souza

Reputation: 970

he limit is related to the datatype itself, not with the fact of being auto increment. You cannot change it.

But, of course you can do some work around...

Like:

An INT will take you up to 2,147,483,647.

So you can start after:

CREATE TABLE [MYTABLE]
(
     [ID] [int] IDENTITY(600000,1),
     .....
)

Or of course, you can create a constraint

Upvotes: 1

Related Questions