Suppose i have 100 rows in my table, my primary key is integer which is auto incrementing by 1 from 1. I consolidate my data and clear the table once row id reaches 100, will SQL server reuse the deleted primary keys ?
since primary key is incrementing by 1, if the answer of above question is no, what will happen to the next insert, once the primary key reached the biggest possible number an 'Int' can hold ?
MSSQL will not re-use the primary keys that have been deleted using DELETE (I'm assuming you are talking about the identity incrementation.) If you TRUNCATE the table it will reset the seed and reuse them.
If you go over the max for INT it will indeed just fail to make the next row. You can convert your INT column to BIGINT to avoid that.
BIGINT has a max of: 9,223,372,036,854,775,807 and INT has a cap of 2,147,483,647 but note.. you can also use those negative values too!
You can read about those caps here: https://learn.microsoft.com/fr-fr/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017