How to index a table with a slowly changing type 2 size for optimal performance

Question

Suppose you have a table with a slowly changing size of type 2.

We denote this table as follows with the following columns:

* [Key]
* [Value1]
* ...
* [ValueN]
* [StartDate]
* [ExpiryDate]

In this example, suppose that [StartDate] is actually the date at which the values ​​for a given [Key] become known to the system. Thus, our primary key will consist of both [StartDate] and [Key].

When a new set of values ​​arrives for a given [Key], we assign [ExpiryDate] some predefined value with a high surrogate, such as '12 / 31/9999 '. Then we set the existing “most recent” entries for this [Key] to have [ExpiryDate], which is equal to [StartDate] of the new value. A simple connection-based update.

---

So, if we always wanted to get the most recent entries for a given [Key], we know that we could create a clustered index that:

* [ExpiryDate] ASC
* [Key] ASC

(, ), , [ExpiryDate]. , [ExpiryDate] '12/31/9999 ', .

... [Key] s ? , . [StartDate] [ExpiryDate] , [StartDate] [ExpiryDate] , , , . , , [StartDate] , .

---

, , ? , IO, [Key], , , .

, ?

Answer 1

Lazy DBA

? , , , ? , B-Tree "" , ? , , , , .

Ozziemedes http://ozziemedes.blogspot.com/

Answer 2

" ", . , , , . : " https://msdn.microsoft.com/en-us/library/gg492088.aspx" " http://www.nikoport.com/2013/07/05/clustered-columnstore-indexes-part-1-intro/".

, rowstore, , , , . :

    declare @id;
    select @id = min(ID) from table where date = '12/31/9999';
    select fields from table where key = 112 and id > @id;