I have a massive table that is only indexed by the auto-increment column, which is also populated with the current timestamp (which is not indexed).
CREATE TABLE MyTable ( ID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY ,AuditTimestampUtc DATETIME NOT NULL DEFAULT(GETUTCDATE()) ,... )
If I need to query by the creation timestamp of the row, how can I do it efficiently? Adding an index is not feasible because of how gargantuan the table is (hundreds of millions to billions of rows), as we cannot afford the downtime, and I am performing a rare debugging task in a readonly environment, which is essentially
SELECT [...] FROM MyTable WHERE AuditTimestampUtc BETWEEN @Start AND @End
I am trying to debug a novel issue, and have not had to do this task before, so I would have difficulty making the argument for creating a new index. And unfortunately there is quite a process for processing a request to create and sanitize a full database dump (especially given its size), or cloning it to another environment. I have an outdated dump to experiment with, but running the final query will be supervised through a read-only account on production.
Writing a custom binary search seems like overkill, especially in a RDBMS, but alas computers are not mind-readers, even though it is apparent to a person that the identity column can be used as a surrogate ordering* to efficiently search the table by creation time.
*Assuming nobody enables
IDENTITY_INSERT to violate this temporal ordering guarantee.
P.S. I do not believe the database platform is very relevant to this question for the first time ever (ignoring the specific syntactical differences of declaring the index/default constraint/etc) but I am using SQL Server.