Can we find a way to use optimistic...

The way Microsoft documentation online works, there is a dropdown in the left pane of the webpage that lets you select the supported versions.  The pages for 2016, 2017, 2019...

To troubleshoot the performance problem, have you looked at SQL Server waits to get an idea of what resources could be a bottleneck?

https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Also check out what's actually happening right...

Using a SEQUENCE won't necessarily solve the problem of loosing consumed IDENTITY values.  If an INSERT transaction rolls back, or the service restarts:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15#general-remarks

is it possible to write the query in a way that you are just reading the data from the remote server through OpenQuery, then writing it to a local temp...

Here's a link to some documentation about in-memory tables and the feature limitations:

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/unsupported-sql-server-features-for-in-memory-oltp?view=sql-server-ver15

It looks like in-memory and partitioning do not play well together, and there are a number of...

Instead of thinking of it as "what is this table in a data warehouse" think of it more like "what is the business process that I want to model".  Whatever...

The Sort operation is coming into play because it has chosen poorly for which index on ClaimPaidDetail to use.  The way the query is written, it looks like ClaimPaid.ServiceDate looks...

Sorry, I had missed there was a second thread on this item.  I agree with Jeffery Williams comment about determining why it won't use your ServiceDate index.  Looking at the...

Here are some initial observations based on the execution plan:

• There seems to be a large number of reads on the ClaimPaid table, and there is a residual IO warning...

looking at your execution plan, 9 million rows at 15 bytes per row is not very large, so it was able to read the entire table and do the table...

I suppose it depends on your table definitions and the query you are using to "pull maximum values of date fields".  That could mean a number of different things.

If for...

Yes, that makes sense, and that is exactly the situation what I was proposing is designed to avoid.  If your DEVOPS people need different permissions in different environments, just have...

If you store the result in a date typed variable or column instead of VARCHAR, you could use the DATEFROMPARTS function introduced in 2012:

https://docs.microsoft.com/en-us/sql/t-sql/functions/datefromparts-transact-sql?view=sqlallproducts-allversions

I don't think Synonyms would help here, as that would help renaming an object within a database, but not renaming a server.  HandyD's method is actually fairly easy to do,...