Be sure to specify NULL rather than letting nullability default, because the default might be NOT NULL, which would cause an error.

ALTER TABLE #Test2

ADD D int NULL;

Something like below.  It looks the original trigger is an INSTEAD OF INSERT and AFTER UPDATE at the same time.  SQL Server doesn't allow you to define them together.  If...

The pages might "bubble up" one at a time, but that has nothing to do with a "bubble sort", which requires vastly more RAM / storage.

Also, it seems to that...

I don't think so. ...

Something like this:

SELECT A.TicketID, A.Status, A.CreatedDate, A.ResolvedDate,
    (SELECT COUNT(*) FROM dbo.Date_Table DT 
     WHERE DT.Date >= A.CreatedDate AND DT.Date >=...

;WITH cte_date_calcs AS (
    SELECT 
        CASE WHEN todays_day >= 16 
     ...

REORGANIZE is not meant to reduce total / overall I/O, rather it's meant to do it in chunks instead on in bulk, as REBUILD must do it.  You're expecting REORG...

I've found REORGANIZE is often useful when a lot of rows have been deleted / purged from a table.  In that situation, I don't want the other pages messed with...

You shouldn't routinely rebuild or reorganize indexes just because you can.

Far more important is to insure that you have the best clustered index on every existing table.  Naturally that requires...

For a single conditional column comparison, there's no reason to use multiple queries or procs, as it's unnecessarily overly complex.

As to using ISNULL(), that should simply never be used in...

No.

In particular, for example, often an EXISTS() check is more efficient that an INNER JOIN when you just to need to verify that a matching row exists in another table.

You'd also want to keep the closest building id in the table structure (with a datetime of when it was computed) and that distance.

The reason for that is to provide...

--if you just want to see if a dup key exists:
SELECT TOP (1) key_col1, key_col2, COUNT(*) - 1 AS duplicate_count
FROM dbo.table_name
GROUP BY key_col1, key_col2
HAVING COUNT(*) > 1

--if...

Any function against a table column in a WHERE clause is not a good idea and is potentially bad for performance, because it prevents index seeks for that comparison.

https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/

Remember that...

SELECT * 
FROM tblInfo
WHERE @parameter IS NULL OR fldinfo = @parameter