I need an auto increment for the PK such that if the table's primary keys are 1, 2, 3, 4, 5, 6, then the next key should be 7 and then 8 and so on but if they keys are 1, 2, 3, 6 then the next key should be 4 and then 5 and then 7 and so on.
If there is no way to set the auto increment like this, is there at least a way to generate the number 4 using a SQL query in case the keys are 1,2,3,6.
The reason I need this is because a lot of a data gets stored into my db by the minute and it will keep deleting from the middle as well. The ID will hence reach 1 million in no time... and it will have a lot of numbers missing from the middle which i really want to avoid.
No - AutoIncrement is intentionally designed to keep IDs unique at the risk of not being sequential.
I would even avoid using triggers or something to "pack" the missing numbers - you run the risk of two processes trying to insert the same number, performance problems by inserting in the middle of your (presumably) clustered index, etc.
If you're concerned about running out of values, use bigint - it can store 9 trillion trillion values - more than you'd ever be able to consume even if you deleted millions of records a day.