Суррогатные ключи хранилища данных SQL Azure

Это лучший вариант, но вы можете использовать постоянное значение в предложении OVER, чтобы избежать сортировки по определенному значению, и вам не нужно использовать переменную.

INSERT INTO testTgtTable (SrgKey, colA, colB)
SELECT
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) + (SELECT ISNULL(MAX(SrgKey),0) SK FROM dbo.testTgtTable) SK
  , [colA]
  , [colB]
FROM testSrcTable;

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

1. загрузить данные в промежуточную таблицу
2. Выполните поиск MAX() в суррогатном ключе целевой таблицы, чтобы получить текущее максимальное значение.
3. CTAS или вставьте данные из промежуточной таблицы в цель. Добавьте константу max_count к значениям row_number.

Код выглядит примерно так:

DECLARE @max_count bigint
SET     @max_count = (SELECT MAX(ID) FROM Fact)

...

CREATE TABLE Input_Load
WITH (DISTRIBUTION = ROUND_ROBIN
     ,CLUSTERED COLUMNSTORE INDEX
     )
AS
SELECT @max_count + RowNumber
,      ...
FROM   dbo.stage_table
;

Я не думаю, что суррогатные ключи, основанные на хеш-значении бизнес-ключа, являются хорошим решением из-за той самой проблемы, которую вы изложили в отношении коллизий. Это противоречит цели суррогатного ключа, который заключается в предоставлении уникального идентификатора для DW независимо от BK. Все классические проблемы с «интеллектуальными» или «умными» ключами или проблемы, связанные с использованием BK в качестве ПК, по-прежнему будут существовать.

Функция столбца Identity несовместима с операторами CTAS, что значительно уменьшает ее как «решение». Он работает только с INSERTS, UPDATES, DELETES, которые плохо работают в ASDW.

Суррогатные ключи на основе хеша имеет смысл заменить на основе последовательности суррогатными ключами на переход от SMP к хранилищам данных MPP и с внедрение Hadoop, NoSQL и других расширений для работы с большими данными в вашу экосистему бизнес-аналитики.

Вот несколько причин, по которым может потребоваться рассмотреть суррогатные ключи на основе хэшей вместо ключей на основе последовательностей:

• Единый подход к генерации суррогатных ключей на различных платформах в вашей экосистеме бизнес-аналитики. Согласованные ключи на основе хэшей могут быть сгенерированы независимо в различных средах, будь то любой инструмент ETL (SSIS, DataStage и т. д.), любая база данных NoSQL или MPP или реализация Hadoop.

• Суррогатные ключи на основе хеш-логики имеют больше смысла, чем ключи на основе последовательности в реализации ELT, в отличие от ETL. «Загрузить данные, а затем обработать их» (ELT) является предпочтительным способом в решениях MPP и BigData. Процессы загрузки и преобразования данных упрощаются за счет замены поиска вычислением хеш-значения. Как следствие, это переходит от операций с интенсивным вводом-выводом (поиск) к операциям с интенсивным использованием ЦП (генерация хэша).

• Довольно часто все процессы загрузки/преобразования данных могут выполняться полностью параллельно, поскольку можно избежать зависимостей между таблицами, поскольку суррогатные ключи на основе хэшей непротиворечивы и могут генерироваться независимо.

• Довольно часто в сценариях обновления данных в реальном или близком к реальному времени суррогатные ключи на основе хэшей могут генерироваться «на лету», устраняя необходимость в дополнительных поисках, что позволяет пропускать промежуточные области и выполнять вставки непосредственно в таблицы фактов.

• Согласованные суррогатные ключи в средах разработки, UAT и производственных средах.

• Соединения на основе хеш-ключей фиксированной длины оптимальны для большинства платформ хранилищ данных MPP.

Вот несколько предложений:

• Используйте естественный бизнес-ключ в качестве входных данных для хеш-функции для первичного ключа в таблицах измерений.

• Используйте конкатенированные естественные бизнес-ключи, которые составляют первичный ключ, в качестве входных данных для хэш-функции для таблиц фактов. Не забудьте разделить бизнес-ключи в конкатенации по определенному символу, например. |, чтобы избежать случайных столкновений.

• Используйте естественный бизнес-ключ в качестве входных данных для хэш-функции для ссылок на измерения в таблицах фактов.

Однако, как обычно, слово предупреждения! Суррогатные ключи на основе хеша потенциально могут создавать коллизии, т. е. одно и то же значение хеш-функции может быть сгенерировано для двух различных входных значений. Подробнее об этом вы можете прочитать здесь и здесь.

Теперь у нас есть функция Identity Column в хранилище данных SQL Azure. Ссылка