-- 10  Drop NCI, PK and create NC index on ManagedElementAttributeTypeGuid
begin

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

	exec dbo.DBM_Conv_drop_PK 'ManagedElementAttributeData' 
end
go
	-- create for data conversion
	create nonclustered index ManagedElementAttributeData_ManagedElementAttributeTypeGuid_IX on [ManagedElementAttributeData](ManagedElementAttributeTypeGuid)
go



-- 20 Delete data by ManagedElementAttributeTypeGuid
begin

	declare @BatchSize int

	set @BatchSize = 10000

	declare @ManagedElementAttributeTypeGuid uniqueidentifier

	declare cr cursor local fast_forward 
	for
	   select 
	           ManagedElementAttributeTypeGuid
	     from 
		       dbo.ManagedElementAttributeType
		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 ManagedElementAttributeTypeGuid

		fetch next from cr into @ManagedElementAttributeTypeGuid

		if @@fetch_status <> 0 break

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

			delete top(@BatchSize)
			  from dbo.ManagedElementAttributeData
			 where ManagedElementAttributeTypeGuid = @ManagedElementAttributeTypeGuid

			if @@RowCount < @BatchSize break

		end  -- INNER loop by @BatchSize

	end -- OUTER loop by ManagedElementAttributeTypeGuid
	close cr
	deallocate cr


end
go



-- 30.10 -- REBUILD INDEXES - PK
begin

	drop index ManagedElementAttributeData_ManagedElementAttributeTypeGuid_IX on [ManagedElementAttributeData]

	ALTER TABLE [dbo].[ManagedElementAttributeData] ADD  CONSTRAINT [ManagedElementAttributeData_PK] PRIMARY KEY CLUSTERED 
	(
		[ManagedElementGuid] ASC,
		[ManagedElementAttributeTypeGuid] ASC,
		[DateCreated] DESC
	)

end
go

-- 30.20 -- REBUILD INDEXES - NC
begin

	-- create for good
	create nonclustered index ManagedElementAttributeData_ManagedElementAttributeTypeGuid_IX on [ManagedElementAttributeData](ManagedElementAttributeTypeGuid)

end
go
