MDS 2014 : How to Create Custom Index on MDS attributes

Hi All

we need to Create index on some of the Matching attributes in our Customer Entity. It is not straight forward in MDS DB , so for that we need to do the fallowing steps

Step1 : Get the Entity ID and EntityTable metadata from mdmtblEntity  table in MDS DB
Step2 : Need to Get the attribute tablecolumn from mdm.tblAttribute table .

we can create a Dynamic query to create custome index when you know EntityName and attributes names.

Dyanmic query



USE MDS
GO
Declare @SQL Varchar(2000), @f int =0
Declare  @i int, @S Varchar(50)
Select @S=EntityTable , @i=ID From mdm.tblentity where name='Customer'

If Exists (Select Name From Sysindexes Where Name='IX_Customer_MatchAttributes_001')
Begin
Set @SQL ='Drop Index IX_Customer_MatchAttributes_001 on mdm.' +  @S
exec  (@SQL)
set @f=1
End
Set @SQL   ='CREATE NONCLUSTERED INDEX IX_Customer_MatchAttributes_001
ON [mdm].'
Set @SQL= @SQL + '['+  @S +'] ('
Select @SQL = @SQL + '['+ Tablecolumn +']' From mdm.tblAttribute  where Name in ('matchAddress') and Entity_ID=@i
Select @SQL = @SQL + ','+  '['+ Tablecolumn +']  )
Include ('  From mdm.tblAttribute  where Name in ('matchPostCode') and Entity_ID=@i
Select @SQL=@SQL + '['+  Tablecolumn +']' From mdm.tblAttribute  where name in ('matchNameFirst' ) and Entity_ID=@i
Select @SQL=@SQL + ',['+  Tablecolumn +'] ' From mdm.tblAttribute  where name in ('nameLast' ) and Entity_ID=@i
Select @SQL=@SQL + ',['+  Tablecolumn +'] ' From mdm.tblAttribute  where name in ('emailAddress' ) and Entity_ID=@i
Select @SQL=@SQL + ',['+  Tablecolumn +'] ' From mdm.tblAttribute  where name in ('matchPhoneHome' ) and Entity_ID=@i
Select @SQL=@SQL + ',['+  Tablecolumn +'] ' From mdm.tblAttribute  where name in ('matchPhoneMobile' ) and Entity_ID=@i
Select @SQL=@SQL + ',['+  Tablecolumn +'] ) ' From mdm.tblAttribute  where name in ('MemberNumber' ) and Entity_ID=@I
exec (  @SQL )
--select @SQL

if @f=0
Begin
If Exists (Select Name From Sysindexes Where Name='IX_Customer_MatchAttributes_001')
Print 'Sucessfully Created Index IX_Customer_MatchAttributes_001'
End

if @f=1
Begin
If Exists (Select Name From Sysindexes Where Name='IX_Customer_MatchAttributes_001')
Print 'Sucessfully Re-Created Index IX_Customer_MatchAttributes_001'
End