dbo.QBM_PIndexCreate

1    create   procedure QBM_PIndexCreate (@Tablename varchar(30) , @IndexName varchar(30) , @IsUnique bit , @Columns varchar(max)  , @Includes varchar
2(max) = ''  ) as begin declare @existing table (IndexName varchar(256) collate database_default  , IsPK bit , IsUnique bit , HasMoreColumns bit , HasAllIncludes
3 bit , HasMoreIncludes bit ) declare @SqlCmd nvarchar(max) declare @Pattern nvarchar(max) declare @DebugSwitch int = 0 declare @DebugExistingname varchar
4(30) declare @Message nvarchar(1000) SET XACT_ABORT OFF BEGIN TRY if not exists (select top 1 1 from sys.tables t where t.name = @Tablename and t.is_memory_optimized
5 = 0 ) begin goto endlabel end if exists (select top 1 1 from sys.tables t where t.name = 'TMPExcessQuantity' ) begin select @SqlCmd = concat( 'update TMPExcessQuantity
6		set Touched = 1
7		where ObjectClass = ''Index''
8		and TableName = '''
9, @Tablename, '''
10		and ElementName = ''', @IndexName, ''' ' ) exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 250 , @MaxWaitTimeForLock_s
11 = 1 , @ProcIDForJournal = @@procid , @HandleErrorSilent = 1  , @ExecuteWithTransact = 0 end select @Pattern = concat('create ' , case @IsUnique when 1
12 then 'unique' else '' end , ' index "@IndexName" on @Tablename (@Columns) ' ) if @Includes > ' ' begin select @Pattern = @Pattern + '	include (@Includes)'
13 end select @Pattern = @Pattern + ' with (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =  OFF )' insert into @existing(IndexName, IsPK, IsUnique, HasMoreColumns
14, HasAllIncludes, HasMoreIncludes) select e.IndexName, e.IsPK, e.IsUnique, e.HasMoreColumns, e.HasAllIncludes, e.HasMoreIncludes from dbo.QBM_FTIndexesForColumns
15 (@TableName, @Columns , @Includes ) e select top 1 @Message = concat('#LDS#Indexname {0} is too long.|', e.Indexname, '|') from @existing e where len(e.IndexName
16) > 30 if @Message > ' ' begin raiserror(@message, 18, 1) with nowait end if exists (select top 1 1 from @existing e where e.IndexName = @IndexName  and
17 e.HasMoreColumns = 0 and e.HasAllIncludes = 1 and e.HasMoreIncludes = 0 and e.IsUnique = @IsUnique ) begin  if exists(select top 1 1 from @existing e 
18where e.IndexName <> @IndexName and (e.IsUnique = @IsUnique or @IsUnique = 0)  and e.HasAllIncludes = 1   and @IndexName not in ( 'QBM_XI7JobQueue' ,'SAC_XC1SAPGroupHasAuthObjectEl'
19 ) ) begin  if @DebugSwitch > 0 begin print 'Fall 1b - dropppen' end exec QBM_PIndexDrop @Tablename, @Indexname goto endLabel end else begin  if @DebugSwitch
20 > 0 begin print 'Fall 1a - nichts zu tun' end goto endLabel end end if exists (select top 1 1 from @existing e where e.IndexName = @IndexName and (e.IsUnique
21 <> @IsUnique or e.HasAllIncludes = 0 or e.HasMoreColumns = 1 or e.HasMoreIncludes = 1 ) ) begin  if @DebugSwitch > 0 begin print 'Fall 2 - erst mal dropppen'
22 end exec QBM_PIndexDrop @Tablename, @Indexname end if not exists (select top 1 1 from @existing e where e.IndexName = @IndexName ) and exists (select 
23top 1 1 from sys.indexes i join sys.tables t on i.object_id = t.object_id where i.name = @IndexName and t.name = @Tablename  and i.is_hypothetical = 0 
24 and i.type not in (3,4,5,6) ) begin  if @DebugSwitch > 0 begin print 'Fall 2a - erst mal dropppen' end exec QBM_PIndexDrop @Tablename, @Indexname end 
25 select @DebugExistingname = null select top 1 @DebugExistingname = e.IndexName from @existing e where (e.IsUnique = @IsUnique or @IsUnique = 0)  and e.HasAllIncludes
26 = 1  and e.IndexName <> @IndexName  and @IndexName not in ( 'QBM_XI7JobQueue' ,'SAC_XC1SAPGroupHasAuthObjectEl' ) if @DebugExistingname > ' ' begin if
27 @DebugSwitch > 0 begin print 'Fall 3 - ' + @IndexName + ' ist Teilmenge von existing ' + @DebugExistingname + '(' + @columns end goto endLabel end   if
28 @DebugSwitch > 0 begin print 'Fall 4, durchläufer' select * from @existing end CreateIt: select @SqlCmd = replace(replace(replace(replace(@pattern , '@indexname'
29, @IndexName) , '@Tablename', @Tablename) , '@Columns', @Columns) , '@Includes', @Includes) if @DebugSwitch > 0 begin print 'anlegen' print @sqlcmd end
30  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s = 10  , @ProcIDForJournal = @@procid , @HandleErrorSilent
31 = 0  , @ExecuteWithTransact = 0 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return end
32 
33