SQL Server – How to Terminate BCP Output with Specific String and Newline

Ever is a big word, but, in general, no, I wouldn't put a varchar(2000) field into an INCLUDE.

And yeah, the way that data is stored at the page level can seriously impact performance of the index, depending on how the index is used.

The thing is, the more rows of data you can cram into a page, the fewer pages have to get accessed, the faster your system is, for the most part. Adding a really large column means less information stored on a page, so, in the event of range seeks or scans, more pages have to be read to retreive the data, seriously slowing stuff down.

To know for sure if this is an issue on your query, or on your system, you'd have to monitor the reads, especially the number of pages that the query uses.

Interesting problem. Here is my attempt.

First we calculate a running total of the Allocation.InvoiceAmt over the various InvoiceId:

WITH inv AS
  ( SELECT 
      InvoiceId, Account, 
      InvAmt = InvoiceAmt,
      SumInvAmt = SUM(InvoiceAmt) OVER 
        (PARTITION BY InvoiceId 
         ORDER BY Account
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW)
    FROM Allocation 
  ) 

and the same for Payment.Amount:

, pay AS
  ( SELECT 
      PaymentId, InvoiceId, PaymentType, CheckNumber, 
      PayAmt = Amount,
      SumPayAmt = SUM(Amount) OVER 
        (PARTITION BY InvoiceId 
         ORDER BY PaymentId
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW)
    FROM Payment 
  )

Then we combine the previous two CTEs:

SELECT 
    inv.InvoiceId,
    pay.PaymentId,
    inv.Account,
    PaymentAllocated = 
      CASE WHEN SumPayAmt <= SumInvAmt - InvAmt
             OR SumInvAmt <= SumPayAmt - PayAmt
      THEN 0
      ELSE
          CASE WHEN SumPayAmt <= SumInvAmt THEN SumPayAmt      -- these would be
               ELSE SumInvAmt END                              -- simpler if there
        - CASE WHEN SumPayAmt-PayAmt <= SumInvAmt-InvAmt       -- was a LEAST() and 
               THEN SumInvAmt-InvAmt                           -- a GREATEST() function
               ELSE SumPayAmt-PayAmt END
      END
FROM inv JOIN pay
  ON inv.InvoiceId = pay.InvoiceId
ORDER BY 
    inv.InvoiceId,
    pay.PaymentId,
    inv.Account ;

Test at SQL-Fiddle