I am designing a spatial database schema for SQL Server 2016 with a table that is going to hold a large amount of rows (50+ billions, increasing by 50+ millions per day).
- Spatial queries must be fast
- Temporal queries (on a timestamp column) must be fast
- The table must be partitioned over the timestamp column
The schema might look something like this:
With this schema, I can not use a clustered columnstore index due to the Position column which is a spatial type. My gut reaction to this was to split out the Position column to a new table with a 1-to-1 relationship.
However, this also introduces some problems. The spatial index will not be partitioned, unless I partition the second table. To do that, I will have to duplicate the timestamp column.
Is there a better way to achieve a partitioned clustered columnstore index for this dataset?