-- 10  Drop existing NCI and create new NC index on ManagedElementTypeGuid
begin

    exec [dbo].[DBM_Conv_drop_IX_AllForTable]
													@tablename     = 'ManagedElement'
												   ,@excludeUnique = 0

    -- create													
	create nonclustered index ManagedElement_ManagedElementTypeGuid_IX on [ManagedElement](ManagedElementTypeGuid)

end
go


-- 20 Delete data by ManagedElementTypeGuid
begin

	declare @BatchSize int

	set @BatchSize = 10000

	declare @ManagedElementTypeGuid uniqueidentifier

	declare cr cursor local fast_forward 
	for
	   select 
	           ManagedElementTypeGuid
	     from 
		       dbo.ManagedElementType
		where
				ManagedElementTypeGuid  in 
				(
					 'F58F87D0-3906-4065-8A45-CF3AE7801B9E' /* select dbo.udf_ManagedElementTypeGuid_SoftwareAsset() */
					,'59001058-9DCC-4341-907D-4056BCF6E418' /* select dbo.udf_ManagedElementTypeGuid_Win32_Service() */
					,'582385EC-AB5B-4C93-B0CC-18F51B3A4B07' /* select dbo.udf_ManagedElementTypeGuid_Win32_QuickFixEngineering() */
				)

	open cr
	while 1 = 1 begin  -- OUTER loop by ManagedElementTypeGuid

		fetch next from cr into @ManagedElementTypeGuid

		if @@fetch_status <> 0 break

		while 1 = 1 begin  -- INNER loop by @BatchSize

			delete top(@BatchSize)
			  from dbo.ManagedElement
			 where ManagedElementTypeGuid = @ManagedElementTypeGuid

			if @@RowCount < @BatchSize break

		end  -- INNER loop by @BatchSize

	end -- OUTER loop by ManagedElementTypeGuid
	close cr
	deallocate cr


end
go


-- 30 -- REBUILD INDEXES 
begin


	ALTER INDEX ManagedElement_PK                                                                                    ON [dbo].[ManagedElement] REBUILD
	ALTER index ManagedElement_ManagedElementTypeGuid_IX                                                             on [dbo].[ManagedElement] REBUILD

	CREATE NONCLUSTERED INDEX [ManagedElement_DeviceGuid_ManagedElementTypeGuid_DateDeleted_ManagedElementTypeGuid_DateCreated_IX] ON [dbo].[ManagedElement]
	(
		 [DeviceGuid] 
		,[ManagedElementGuid] 
		,[DateDeleted]
		,[ManagedElementTypeGuid] 
		,[DateCreated] 
	)

end
go

