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