Мой упрощенный и надуманный пример таков:

Допустим, я хочу измерять и хранить температуру (и другие значения) во всех городах мира на ежедневной основе. Я ищу оптимальный способ хранения данных, чтобы было так же легко получить текущую температуру во всех городах, как это было бы для исторического получения всей температуры в одном городе.

Это достаточно простая проблема, но я ищу лучшее решение.

Вот два основных варианта, о которых я могу думать:

Вариант 1 - та же таблица хранит текущие и исторические записи

Храните все текущие и архивные записи в одной таблице.

то есть

CREATE TABLE [dbo].[WeatherMeasurement](
  MeasurementID [int] Identity(1,1) NOT Null,
  TownID [int] Not Null,
  Temp [int] NOT Null,
  Date [datetime] NOT Null,
)

Это бы все упростило, но какой был бы наиболее эффективный запрос для получения списка городов и текущей температуры? Будет ли этот масштаб, если в таблице есть миллионы строк? Есть ли что-то, что можно получить, установив в таблице какой-нибудь флаг IsCurrent?

Вариант 2 - Хранить все архивные записи в отдельной таблице

Там будет таблица для хранения текущих измерений в реальном времени

CREATE TABLE [dbo].[WeatherMeasurement](
  MeasurementID [int] Identity(1,1) NOT Null,
  TownID [int] Not Null,
  Temp [int] NOT Null,
  Date [datetime] NOT Null,
)

И таблица для хранения исторической архивной даты (возможно, вставленной триггером)

CREATE TABLE [dbo].[WeatherMeasurementHistory](
  MeasurementID [int] Identity(1,1) NOT Null,
  TownID [int] Not Null,
  Temp [int] NOT Null,
  Date [datetime] NOT Null,
)

Это дает преимущества, заключающиеся в том, что основные текущие данные остаются простыми и очень эффективными для запросов за счет того, что схема становится более сложной и вставка данных обходится дороже.

Какой самый лучший вариант? Есть ли лучшие варианты, которые я не упомянул?

ПРИМЕЧАНИЕ. Я упростил схему, чтобы лучше сфокусировать свой вопрос, но предположим, что каждый день будет вставляться много данных (100 000 записей), и данные актуальны в течение одного дня. Текущие данные будут так же вероятны, как и исторические.