
---Fix MW-5069:
------ For Alex: the same unique indexes should be modified to non-unique in the MWData Upgrade script 7400_101-Asset_20-Tables_20-Express.sql	
-- The the unique index Asset_Win32_Service_DeviceGuid_Name_UX should be removed from Asset_Win32_Service table, there is no such index in SCData database.
-- When upgrading MWData database, we should:
--	1. remove the unique index Asset_Win32_Service_DeviceGuid_Name_UX
--	2. create the non-unique index Asset_Win32_Service_DeviceGuid_Name_IX
if exists
	(
    SELECT *
      FROM  sys.indexes AS i WITH (NOLOCK)
     where 
            i.[name]      = 'Asset_Win32_Service_DeviceGuid_Name_UX'
        and i.[object_id] = object_id('Asset_Win32_Service')               
    )
begin 
	drop INDEX [Asset_Win32_Service_DeviceGuid_Name_UX]
		ON [dbo].[Asset_Win32_Service]
end -- if ( 1 = dbo.udf_Conv_DoesIndexExist('Asset_Win32_Service', 'Asset_Win32_Service_DeviceGuid_Name_UX'))

if not exists
	(
    SELECT *
      FROM  sys.indexes AS i WITH (NOLOCK)
     where 
            i.[name]      = 'Asset_Win32_Service_DeviceGuid_Name_IX'
        and i.[object_id] = object_id('Asset_Win32_Service')               
    )
begin 
	CREATE NONCLUSTERED INDEX [Asset_Win32_Service_DeviceGuid_Name_IX]
		ON [dbo].[Asset_Win32_Service]([DeviceGuid] ASC, [Name] ASC);
end -- if ( 0 = dbo.udf_Conv_DoesIndexExist('Asset_Win32_Service','Asset_Win32_Service_DeviceGuid_Name_IX'))
go




-- For the same reason, when upgrading MWData database we should:
--	1. remove the unique index Asset_Win32_QuickFixEngineering_DeviceGuid_HotFixID_IX
--	2. create the non-unique index Asset_Win32_QuickFixEngineering_DeviceGuid_HotFixID_IX
if exists
	(
    SELECT *
      FROM  sys.indexes AS i WITH (NOLOCK)
     where 
            i.[name]      = 'Asset_Win32_QuickFixEngineering_DeviceGuid_HotFixID_IX'
        and i.[object_id] = object_id('Asset_Win32_QuickFixEngineering')    
        and i.is_unique = 1
    )
begin 
	drop INDEX [Asset_Win32_QuickFixEngineering_DeviceGuid_HotFixID_IX]
	    ON [dbo].[Asset_Win32_QuickFixEngineering]
end -- if ( 1 = dbo.udf_Conv_DoesIndexExist('Asset_Win32_QuickFixEngineering', 'Asset_Win32_QuickFixEngineering_DeviceGuid_HotFixID_IX'))

if not exists
	(
    SELECT *
      FROM  sys.indexes AS i WITH (NOLOCK)
     where 
            i.[name]      = 'Asset_Win32_QuickFixEngineering_DeviceGuid_HotFixID_IX'
        and i.[object_id] = object_id('Asset_Win32_QuickFixEngineering')               
    )
begin 
	CREATE NONCLUSTERED INDEX [Asset_Win32_QuickFixEngineering_DeviceGuid_HotFixID_IX]
		ON [dbo].[Asset_Win32_QuickFixEngineering]([DeviceGuid] ASC, [HotFixID] ASC);
end -- if ( 0 = dbo.udf_Conv_DoesIndexExist('Asset_Win32_QuickFixEngineering','Asset_Win32_QuickFixEngineering_DeviceGuid_HotFixID_IX'))  

--- End fix MW-5069


