I'll pile on and also agree that in your situation it doesn't sound like there's a pressing need to eliminate gaps; it sounds very much like a solution in search...

Both losing committed changes and having the data unavailable for any changes are generally undesirable, of course, but the latter is still rather different from losing committed changes.

Nobody...

It looks like you're just getting lucky, in this case. A substantial bit of the performance improvement is going to be from implementing the DISTINCT with a hash match instead...

Yeah, UPPER has some unfortunate side-effects. It's non-SARGable, so limits how supporting indexes can be used.

Similarly, it also messes with use of statistics, which means it might pick...

Another way, assuming we're actually on 2012:

UPDATE ft
SET Team_SID=fv
FROM (SELECT *,fv=FIRST_VALUE(Team_SID) OVER (PARTITION BY Employee_SID ORDER BY CreationDate DESC)
  FROM FactTable) ft

I'm guessing what is desired is for each invoice number, return the sum of each of the fare, commission, and gst columns, along with the "first" non-blank passenger_name associated with...

Over all the DBs in the world I wouldn't be surprised if most DBs >=1 TB are non-OLTP, but I work with some OLTP DBs of that size.

The...

Heh, I hate to be that guy, but now the query actually returns correct results with this particular data because multiply and divide have the same precedence, so they're evaluated...

Yes, quite likely it is worth it. 

Those are very high counts and would be affecting performance of the queries involved pretty drastically, unless it was some odd case...

Yeah, you can use sys.dm_db_index_operational_stats for that.

Something like this:

SELECT database_name=DB_NAME(database_id),
   table_name =OBJECT_NAME(object_id, database_id),
   forwarded_fetch_count
FROM  sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
WHERE  index_id=0 AND forwarded_fetch_count>0
ORDER BY...

There are some decent general techniques for handling this, but I'm still not clear on the logic you want to implement.

For the Identifier ending in 792, for example,...

Heh, well, to be fair, the question is incredibly vague. 

It's "Why do I get the result I get?"

The are many factors that combine to get this...

Also, just as a note, please do not post the same topic multiple times. 

You've posted several versions of this question under sub-forums for different versions of SQL Server.