You could have the vendor modify the replication to include the non-clustered indexes for those tables you have identified - or you can add them to the replicated database.

Creating your...

So what indexes are missing?  Review the table(s) on the source system...

Maybe this will give you some ideas:

Declare @testTable Table (column_a int, column_b int);

 Insert Into @testTable (column_a, column_b)
 Values (10,  3)
     ...

Thanks for making your time for reply Chris!!

Is this just a one way transfer of information, from Database1 to Database2, or is there a need to also send updates...

So - you were using a restored copy of the database and these queries ran in an acceptable time.  Correct?

Now - on the new replicated database the same queries take...

It is still listed in the SQL Server 2019 documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15

The point is that the IDENTITY values will be computed based on the order - but how that data is...

Okay - you definitely have a problem with this script.  In fact, I can see several issues...

This code:

IF DATEPART(HOUR, GETDATE()) = 7
SET @BackupType = 'FULL'
ELSE
SET @BackupType =...

Something like this?

Declare @testTable Table (CombinedData varchar(100));

 Insert Into @testTable (CombinedData)
 Values ('Ace Master')
      , ('Agreement (IRE)')
     ...

We need to see the code - and how that code is selecting the databases to be backed up.  I would suspect there is some criteria that is excluding those...

I cannot help you without understanding the goal of your request.  If you can answer why you need to do this and what problem you are trying to resolve -...

Thanks for the reply Williams!!

Actually i am not sure it is that much complex to write a trigger to delete/update/insert when tables in database1 is changed need to automatically...

Is the SQL Agent Job running from a sysadmin account - or a user account?  If a user account then that account probably does not have access to the databases...

Even worse - are you using INIT on the backup device for your differentials and transaction log backups?  If so - then you are already at risk for significant data...

Hi Williams,

Thanks for making your time for reply!!

why do you need a copy of the data in the secondary database?

My reply:-Actually my task was to update  the data across...

For completeness sake - here is the query using PIVOT:

 --==== Using PIVOT and datediff / 30 to determine category
 Select pvt.Cust_num
     ...