MDM tables are essentially repurposed junk dimensions. We will talk about the mechanics of how MDM tables work in a later section.
Regardless of what their data type is in the source system, values should be stored as NVARCHAR so they can be hashed without complaint.
You'll notice that the primary key does not start at 1. You do not want an enterprise key that is that simple. Throw some zeros on it and make it a real company.
USE ODSGODROPINDEXIFEXISTS [NCIDX_YourDimensionNameMasterData_RowHash] ON [mdm].[YourDimensionNameMasterData]GOSETARITHABORTONSETCONCAT_NULL_YIELDS_NULLONSETQUOTED_IDENTIFIERONSETANSI_NULLSONSETANSI_PADDINGONSETANSI_WARNINGSONSETNUMERIC_ROUNDABORTOFFGODROPTABLEIFEXISTS [mdm].[YourDimensionNameMasterData]GOSETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [mdm].[YourDimensionNameMasterData]( [YourDimensionNameEK] [bigint] IDENTITY(1000,1) NOT NULL, [SourceSystemKey1] [nvarchar](50) NOT NULL, [SourceSystemKey2] [nvarchar](50) NOT NULL, [RowHash] AS (CONVERT([binary](35),hashbytes('SHA1',concat(CONVERT([nvarchar](35),[SourceSystemKey1],(0)),CONVERT([nvarchar](35),[SourceSystemKey2],(0)) )),(0))) PERSISTED,CONSTRAINT [PK_YourDimensionNameMasterData] PRIMARY KEYCLUSTERED( [YourDimensionNameEK] ASC)WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON, OPTIMIZE_FOR_SEQUENTIAL_KEY =OFF) ON [PRIMARY]) ON [PRIMARY]GOCREATEUNIQUENONCLUSTEREDINDEX [NCIDX_YourDimensionNameMasterData_RowHash] ON [mdm].[YourDimensionNameMasterData]( [RowHash] ASC)WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, SORT_IN_TEMPDB=OFF, IGNORE_DUP_KEY=OFF, DROP_EXISTING=OFF, ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON, OPTIMIZE_FOR_SEQUENTIAL_KEY =OFF) ON [PRIMARY]GO
Column Name
Data Type
Function
SourceSystemN
NVARCHAR(N)
Holds one of the primary keys that make up the set of keys that uniquely define a complete master data record.
RowHash
BINARY(16)
A computed value of those columns used to compute the uniqueness of a row. This value is used to determine INSERT or UPDATE operations on the table.