18

Initially, I assume that PostId is auto-incremented. Except for very rare cases, where the data is completely wiped from the database, leaving some holes in the PostId progression, I expect that the PostId would always lead me to a question, an answer, or a tag wiki.

However, when I looked at the SEDE data as research for this meta question, with the following query, I found some big gaps in the progression:

;WITH n(n) AS
(
    SELECT 1
    UNION ALL
    SELECT n+1 FROM n WHERE n < 26000
)
SELECT n FROM n
left join postswithdeleted d on d.id = n
where d.posttypeid is null
ORDER BY n
OPTION (MAXRECURSION 26000);

On A&M, there are roughly 6000 ids not accessible by any means, broken into 6 ranges, roughly 1000 ids each:

  • 934 → 1929 (996 ids), post id 933 posted on 2013/01/14
  • 10160 → 11154 (995 ids), post id 10159 posted on 2014/06/08
  • 11751 → 12746 (996 ids), post id 11750 posted on 2014/07/10
  • 15640 → 16636 (997 ids), post id 15639 posted on 2014/12/11
  • 17353 → 18351 (999 ids), post id 17352 posted on 2015/01/07
  • 24612 → 25609 (998 ids), post id 24611 posted on 2015/09/03

Since the gaps are quite uniform, I guess there must be a reason behind it. What caused these gaps in the progression of PostId?

Improve this question
5
  • for those to lazy to copy and paste like i wanted to be, here's a query link
    – CRABOLO
    Oct 15, 2015 at 10:56
  • I assume the field is auto increment, and the issue described here happens in the live database.
    – Shadow Wizard Is Sad And Angry
    Oct 15, 2015 at 11:35
  • @ShadowWizard: That seems to be the cause. Do you mind posting an answer?
    – nhahtdh
    Oct 15, 2015 at 11:38
  • @nhahtdh not 100% sure, but will post answer soon when having some time.
    – Shadow Wizard Is Sad And Angry
    Oct 15, 2015 at 11:53
  • You may want to compare the dates of existing posts around these gaps with the notices on StackStatus
    – user259867
    Oct 15, 2015 at 13:14

1 Answer 1

Reset to default
15

As far as I can see, this is internal issue of SQL Server 2012 and newer, which is nicely explained in the answer to this question on Stack Overflow:

Microsoft has changed the way they deal with identity values in SQL Server 2012 and as a result of this you can see identity gaps between your records after rebooting your SQL server instance or your server machine. There might be some other reasons for this id gaps, it may be due to automatic server restart after installing an update.

Most likely, all those "gaps" you noticed happened when the live SQL Server was restarted.

Since there is no real impact and no data loss, SE team probably prefered not to spend any time on "fixing" this, following the "If it ain't broke, don't fix it" agenda. :)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .