Let’s say you have a table of invoices
CREATE TABLE Invoice ( InvoiceID int IDENTITY(1,1) PRIMARY KEY, InvoiceDate DateTime CONSTRAINT [df_invoicedate] DEFAULT GETDATE(), .... )
The invoice table has an
InvoiceDate which is always the current date/time when the record is inserted.
Let’s say you index this date
CREATE INDEX idx_InvoiceDate ON Invoice(InvoiceDate)
Note this is a non-clustered index (held in a separate file).
Let’s say we know the RDBMS uses B+tree format for index files.
Bearing in mind that all inserts will hit the same page of the index file (because they are always at the end of the sort order), and that inserts into a single location are worst-case for B+tree insert, what special considerations should be observed?
Will this index end up lopsided? Should it be reindexed regularly? Should I use a low, normal, or high fill factor?
Should we try to find something else to put into the index prior to the date, e.g. an invoice type, to get the tree to balance out more evenly?
Will there be issues with hotspots or index page contention, because all inserts hits the same part of the index? What can I do to mitigate that?
I understand that for an ascending surrogate key, a reverse key index might be better. But this is a date and I may want to perform scans of a date range. Is there anything I can do that will alleviate any of the issues that reverse key indices are meant to address?
Or is all of this a total non-issue, and indices of this kind are fine and I should stop worrying about it?
Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.