When I used ERWin (as they spelled it at the time, IIRC) I thought it was excellent tool (once I got used to it).  However, you have to understand data...

Actually, Sergiy, for certain calcs -- such as computing interest on a house loan in some places -- it's illegal to use more than 4 digits of accuracy.

So,...

For DELETE, a DENY DELETE should work.

For DROP, if the user created the table, they likely own it, so you'd have to use a DDL trigger for that AFAIK.

Do you have a q?

Actually, Sergiy, for certain calcs -- such as computing interest on a house loan in some places -- it's illegal to use more than 4 digits of accuracy.

So, if the...

Just change the clustered index.  The other method is too much overhead and too much of a pain.  A clus index change is a one-time thing.  All the create-a-temp-table-and-then-join-to-it has...

How is the orders table clustered?  I'm guessing by orderid.

But, for this query, since jobdatestart is the key restrictor on the number of rows, that would make a better clustering...

I believe the +/- n days works with any datetime value.  I 'm...

Do you have the latest CU applied?  That condition is often a bug in SQL code, fixed by maintenance.

You don't need to shrink the database, but you do need shrink the affected file(s).  NEVER shrink a database, only file(s).

Run:

DBCC SHOWFILESTATS

and find the FileId(s) with lots of free space. ...

I think maybe you can just change the CROSS APPLY in Steve's code?!:

...
     cross apply (values (cast(w.CREATE_DATE as datetime)+
    ...

(1) Cluster the main table on ID: a nonclus index is worthless here.

(2) Cluster the lookup table on ID.  The join of the two tables should now become a MERGE...

Sure, you can do that.  But I can't really follow the logic of the the original statement -- particularly the logic of starting from a negative starting byte, so I...

Sorry, I left out a step.

dbo.DelimitedSplit8k is a function commonly used on this site to efficiently split a string into multiple parts based on a single-character delimiter.

Here's a version of...

SELECT PERSONNUM,ORGPATHTXT,LOC    
FROM SampleData2
CROSS APPLY (
    SELECT TOP (1) LOC
    FROM (
      ...