CODEWITHSUNDEEP
sql-server
Evan Payne
Evan Payne

Reputation: 181

Is there anyway to indicate to SQL Server that a column is ever increasing?

I'm working with a table that contains roughly one billion rows. It has a timestamp column that is, by its nature, ever increasing.

If I were to query this table and ask for records that were created in the past 5 minutes (using the timestamp in the where clause), SQL Server will scan an entire index to get the results and it could take several minutes.

However, because this column is ever increasing and therefore ordered, SQL Server could use a more sophisticated search technique to find these records, as if it were an index. I understand that SQL Server doesn't know these columns are ordered (outside of some stats/distribution info), and it cannot guarantee that they're ordered, either, so it cannot do this.

Is there perhaps an "ever increasing" constraint, whereby any new rows added have to have a timestamp column value that is more than or equal to the previous one? It seems like there should be a way to take advantage of the natural order of this column.

Upvotes: 1

Views: 124

Answers (1)

Radek Gąska
Radek Gąska

Reputation: 56

Unfortunately there is no such constraint/option/etc. SQL server does not know what is relationship between tables or indexes... To be honest I think you should consider creating an index on that column. The other option would be to create partitions on that table based on the timestamp column, then you should be able to force SQL to scan only given partition. However new index is much simple and more flexible change.

Upvotes: 1

Related Questions