You must Sign In to post a response.
  • Category: SQL Server

    How to set maximum value for Identity Column

    How to set maximum value for Identity Column in Sql Server
  • #744039

    CREATE TABLE tbl (
    ID int PRIMARY KEY identity(1,1)

    if i want to set Max value for id is 2000 means,

    alter table tbl add constraint CHK_TBL_MAX_ID check (id <= 2000)

  • #744043
    if you are using int then the max value is 2,147,483,647 for integer.
    If you want the set your own value you can CHK_TBL_MAX_ID you can use the following query to modify the value
    alter table table name add constraint CHK_TBL_MAX_ID check (id <= set your value required)

  • #744052
    What is the purpose of set max value for identity column. if it is int type than every body know the max limit of int datatype so you can use that one if you want some condition based on that.
    Regards & thanks
    Arvind kumar

  • #744065

    Create table table_name (
    Countname1 int not null identity(1,1) constraint Countname1 primary key check (Countname1 <= 10000),
    Countname2 varchar(10)

    its may useful for u..

    using INT data type - its 4 Bytes,if cross the max limit get an over flow error.

    better go for Bigint data type and its 8 Bytes..

  • #744068
    Hai Kali Raj,
    There are various ways through which you can set the value of your IDENTITY column in SQL Server database.
    1. You can use the Constraints which will be attached to your table and check for the value
    2. You can use the trigger which will fire for each of the insert statement and when reaches to max value, throws the error.
    Hope it will be helpful to you.

    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)

  • #744072

    here is a smaple code for the same.

    DBCC CHECKIDENT('product', RESEED, 11111111)

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #744097

    I can't get your point, but as of my understanding the post I'm assuming you need to set the id value as you desired right.?

    Refer below sample

    set indentity_insert on
    -- insert statement in that mentioned the identity column with your expecting value.
    set identity_insert off

    Try something like above to achieve your goal.

    Hope this will help you to resolve the issue...

    Give respect to your work, Instead of trying to impress your boss.

    Blog :

  • #744796

    Create Table emplyoee(

    emplyoeeId int PRIMARY KEY identity(1,1)

    Set max value

    alter table emplyoee add constraint CHK_TBL_MAX_emplyoeeId check (id <= 100)

    Dipti Choudhari

  • Sign In to post your comments