Sql-server – limit to the number of objects that can be dropped and recreated (frequently) in SQL Server

sql server

The datatype for field [object_id] in table sys.objects is "int"

So for all the objects in a database this is unique and has a limit of 2,147,483,647 (2 billion'ish)

What happens when there is some process (connecting to this database) that drops and (re)creates a large number of (user defined) tables every hour every day. Does it get to a point, where there can be no more objects created in the database because it ran out of assigning unique object_ids? I read somewhere that sql server does not reuse an object_id that was assigned earlier, if thats the case, it can run out pretty quick, in this situation, right?

Please guide me to some links/articles if this was just a silly question.

Thanks

Best Answer

Looks like the object_ids increase rapidly and roll over frequently. EG

declare @i int = 0
while @i < 10000
begin
  drop table if exists t
  create table t(id int)

  set @i += 1
  if @i % 100 = 0
  begin
    declare @objectid int = object_id('t')
    RAISERROR ('object_id %i', 0 , 1, @objectid) WITH NOWAIT
  end
end

outputs

object_id 834515239
object_id 287037291
object_id 1887042991
object_id 1339565043
object_id 792087095
object_id 244609147
object_id 1844614847
object_id 1297136899
object_id 749658951
object_id 202181003
object_id 1802186703
object_id 1254708755
object_id 707230807
object_id 159752859
object_id 1759758559
object_id 1212280611
object_id 664802663
object_id 117324715
object_id 1717330415
object_id 1169852467
Related Question