We need the full DDL for all tables involved, including indexes, but for now:

1 How is the dmhdb.dbo.Data clustered?

2 The syntax on your update is not best practice and you...

1 The nonclus indexes should be unique, not non-unique.  Worst case, add an identity to the table and use that to make unique non-clus keys.

2 Are the nonclus indexes page...

Create two base source tables: one with only current_record = 'Y' rows, and one with only current_record <> 'Y' rows.  Create a SQL constraint on the each table that enforces...

1.2M rows * 10KB bytes per row is a huge amount of data, ~12GB.  I can imagine it could take 6 mins to transfer that much data.

It would be very...

As I noted above, views sys.foreign_keys and sys.foreign_key_columns should give you FK details.  I can't imagine how your SQL doesn't have those views, since they've been around a long time.

Agreed.

Views sys.foreign_keys and sys.foreign_key_columns should give you FK details.

Also look at sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.

For just any reference to another object, not for a defined relationship, try sys.sql_expression_dependencies.  For example, if...

I prefer this method:

declare
  @start_dt              datetime='2019-12-02',
  @end_dt     ...

You need to be aware that "Execution plan Query cost relative to the batch" is meaningless.  SQL is very often quite wrong on this number.  Just ignore it!

You need to...

Great.  I guess that confirms it gave you the results you needed :-).

LEFT OUTER JOIN (

SELECT *, ROW_NUMBER() OVER(PARTITION BY customer, ApplicationName ORDER BY Created DESC) AS row_num FROM [CIS].[dbo].[patches]

) AS patches ON patches.customer = a.number and patches.ApplicationName = custapps.app AND patches.row_num...

SELECT ...same_as_before_except_add_patches_columns...
FROM customers

LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY customer ORDER BY Created DESC) AS row_num
    FROM [CIS].[dbo].[patches]
) AS...

What I usually do in those situations, if the file's not way too extremely large (2GB max file size; for any size up to 1.5GB, I've found that it takes...

I think this will give you what you need.  I went down to the minute to allow bookings like '10:10' to '10:45'.  You may not have that now, but it...

• This reply was modified 5 years, 11 months ago by ScottPletcher. Reason: Added comment about tally table size
• This reply was modified 5 years, 11 months ago by ScottPletcher. Reason: Changed the tally table to be 10K rows instead of 1000

You're right, you can't avoid dynamic SQL here.  You need dynamic SQL here to insure you get the best query plan.

Steve and Jeff, thank you.  Good to have other eyes on this. 🙂  What should I do with the earlier incorrect code?  Wipe it out?  Should I have kept...