Some of the amounts in the original didn't seem to add up, and the rules weren't specific for FTYPE and FCODE, but here's my best guess so far:

            
May 10, 2021 at 7:14 pm
            
#3881617

            
            
                                                
        

I think a separate schema(s) is a valid approach.  Indeed, it has some advantages.  For one, the developer-created objects are in the db to which they apply.  If you create...

Very busy right now, as soon as I get time, I'll follow up on this ... unless, of course, someone else has already jumped in and solved it.

Create the clustered index only before loading the table.  Create the non-clustered indexes after loading the table.

If this is a permanent table, seriously consider using data compression.  Although it takes...

Yeah.  I'm not sure why MS hasn't changed the SQL Server default value for that setting.  They've made other adjustments to critical starting values, particularly for tempdb, but somehow haven't...

So more logic changes requested by the client.

Similar query but luckily we now have a week indicator with extra columns and new paycodes that should be left out of...

I don't see a huge security issue with a separate schema.  Thus, I don't see why this would necessarily fail an audit.  That seems to be an overly broad claim.

I have...

I go the opposite way.  In development / QA / etc. (i.e., not prod!), I give developers most permissions.  The only thing I prevent -- when I'm allowed to --...

The very best solution to overall performance, and to blocking/deadlocking, is to go back and get the best clustered index keys on every table.  Hint: this will most often not, repeat...

Sorry, I somehow thought was from a different person (pietlinden), totally my fault, I was going on auto-pilot.

Scott - I agree that NOLOCK has its purposes, as do all query hints.  Otherwise they wouldn't be in there to begin with.  I was just trying to...

You don't need the extra check on the flag:

WHERE @myFlag <> 1 OR name NOT IN ('A', 'C')

If the DLRID are mostly unique, or mostly come in batches together when not unique, I suggest encoding the DLRIDs into a numeric code.

That is, create a separate table with...

CXCONSUMER is waiting on parallelism.

Have you adjusted the SQL Server configuration for 'cost threshold for parallelism' to be something larger than the default?  The default is 5, IIRC, which is...

That's easy enough, you can pre-calc a proper date range, using either variables or in a cte.  I'll use variables here just because they're easier to adjust for diff test...