I second the "nonwork_days" table, but I would make sure to design the reason code table well.

You would want to make sure that the reason code table has a...

I very strongly prefer a separate "nonwork_days" table.  The table needs nothing only the date and a tinyint reason code for why that date is a nonwork day.

The big problem...

Partitioning often serendipitously helps with performance.  That's because people will create a different clustered index to help with the partitioning process and that new clustered index actually does help the...

Again, how do you search the table?  That is, what are the common WHERE clauses that you use on the table?  That is what should control the clustering.

If you (almost)...

How do you search the table?  It's likely you would get better performance using partitioning based on how you search the table, esp. if you can partition / cluster first...

My recommendation  would be to read the documentation for BULK INSERT, which is located at the following URL:

https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15

The two keywords to search for in that article are ERRORFILE...

A trigger is the perfect approach for this.  Some developers just irrationally avoid triggers no matter what.

Be sure to specify option FIRE_TRIGGERS in your BULK INSERT...

The duplicate...

The SET applies to all SELECTs.

You're welcome.  I'm very glad it helped.  And thanks for the follow-up and nice feedback!

A trigger is the perfect approach for this.  Some developers just irrationally avoid triggers no matter what.

Be sure to specify option FIRE_TRIGGERS in your BULK INSERT command.

Give me a reason to use the new data type when I can't use date functions with it natively, or can't get DATEDIFF in particular to work with it...

I too don't think OUTER APPLY is the main problem.  But I would strongly urge you to get rid of the function against the table column you are searching on,...

Not much to go on, but, in general, just put the appropriate conditions in the WHERE clause:

DECLARE @key_value ...
DECLARE @col_value ...

SET @key_value = ...
SET @col_value = ...

UPDATE...

Keep in mind, too, that the point of naming is to give accurate business names to objects, not to make things easier for developers.  That is, just because a naming...