Practical Useage Of The Insert-Output Construct

I recently wanted to look into the possibility of using the insert-output construct and did some testing around it.


I created two following procedures and executed them with IO, Time and profile (SET STATISTICS IO, TIME , PROFILE ON)
1.) One with the Insert-output construct

               CREATE PROCEDURE sp_WithOutputCluse
               @FirstName VARCHAR(30)
              ,@LastName VARCHAR(30)
               AS

                INSERT INTO HR.Staff ( FirstName, LastName )
               OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'

               INTO HR.Notification
               (
                  StaffID,
                  NotificationDate,
                  NotificationType
               )
               VALUES ( @FirstName,@LastName);


2.) One with the SET operation to accept the Scope_Identity()

              CREATE PROCEDURE sp_WithOut_OutputCluse
              @FirstName VARCHAR(30)
             ,@LastName VARCHAR(30)
             AS

             DECLARE @StaffID int

             INSERT INTO HR.Staff ( FirstName, LastName )VALUES ( @FirstName,@LastName)
             SET @StaffID = SCOPE_IDENTITY()

             INSERT INTO HR.Notification
             (
               StaffID,
               NotificationDate,
               NotificationType
              )
              VALUES ( @StaffID , DATEADD(d,90,GETDATE()),'90-Day Review')


Following are some of the findings/observations
------------------------------------------------
Limitations
- No FK relationship can be maintained in the table where OUTPUT construct was to be used
- Even though the transaction can be considered atomic , All the internal operations are evaluated/executed serially and the two tables will be locked for the entire duration of the operation
- There is no difference in the IO operations (when compared with the SET = Scope_Identity()operation)

Positives
- There is a significant difference in TIME ( elapsed time)

Conclusion
Taking the above limitations into considerations I would not recommend to use the insert-output construct. As some of the listed limitations are too significant and create enough concern.