Datatype limit in SQL Server 2016

Vijay Kumar 2,031 Reputation points
2021-03-17T23:41:37.417+00:00

Hi team,

some tables in our prodDB are running close or over PKID

given it maybe INT or BIGINT

so in the past we ran over in 2 tables

one being INT, so have to change the type to BIGINT

one being BIGINT have to truncate table

**so the ask is to identiy tables that are close to their limit on their data type on the Identity column**
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,274 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. TheDBLearningGuy 171 Reputation points
    2021-03-18T14:22:26.353+00:00

    Hi @Vijay Kumar ,

    so the ask is to identiy tables that are close to their limit on their data type on the Identity column**

    If you are looking for something like integer columns which are defined as identity and want to know how much percentage is being used, perhaps you should start here and check this -

    https://vyaskn.tripod.com/sql_server_check_identity_columns.htm

    Please let me know if I misunderstood you.

    Thanks.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Guoxiong 8,201 Reputation points
    2021-03-18T00:45:37.71+00:00

    You can find out the ranges of int, bigint, smallint, and tinyint.

    0 comments No comments

  2. EchoLiu-MSFT 14,581 Reputation points
    2021-03-18T06:00:30.793+00:00

    Hi @Vijay Kumar ,

    Sorry, I don't seem to understand your needs very well. If you want to know the length range of data types in sql server, then you can refer to the link provided by Guoxiong. If you want to know the data type of the columns in your table, then you can execute the following code:

    sp_help yourtable  
    

    If none of these suggestions solve your problem, please provide more details.
    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments

  3. Tom Phillips 17,721 Reputation points
    2021-03-18T17:42:22.413+00:00
    0 comments No comments