Negative return value means error happened during the execution.
Unless you explicitly change the meaning of @ReturnValue in the proc code.
Like 
IF @status = 'Feeling Kinda Silly'

It does not matter at all.
FIll Factor is only taken into consideration when an index is rebuilt (reindexed).
If you only drop and re-generate the index, as you mentioned...

Both statements have this in their lock description:

transactionname="implicit_transaction"

It kinda indicates that there is no explicit transaction opened in the code.

The trick is - the both locks are IX.

Which means there...

Can you script this table "for create" and post it here?

Hi,

What is OBJECT_NAME(1666104976) ?

Post the code of the file upload procedure, point out the statement which is executed when the locking happens, and the statements from the app which are blocked.

Don't forget DDL...

Fastest way would be to track the changes at the source (say, by recording pk values for inserted/deleted/updated records in "change" tables) and then copy only changed records over.

Assuming '00' row is mandatory for every group, this should work:

if OBJECT_ID('tempdb..#FileRowGroups') is not null DROP TABLE #FileRowGroups

select IDENTITY(int, 1,1) GroupID, F.RowNo GroupStart,...

3 questions.

1. How many rows are returned by
SELECT * FROM Tbl WHERE Col2 =@P1

2. Do both updates use the same value...

Have an identity column in your staging table - to keep track of original row numbers in the file
CREATE TABLE FileData (RowNo int IDENTITY(1,1) PRIMARY KEY NOT...