Somehow you left out the "SET @MyBit = 0" on one function?

Except stay completely away from INFORMATION_SCHEMA views, which are more overhead anyway.  Use sys.tables, sys.columns, etc., instead.

And what would you want to name the view?

Sample data and results are almost always clearer than just a textual explanation.  I hope this matches what you need:

SELECT
    ISNULL(t2.CarColor, t1.CarColor) AS CarColor,
...

IF I understand the q correctly, then:

SELECT
    ISNULL(t2.CarColor, t1.CarColor) AS CarColor,
    t1.CarName,
    t1.CarStatus
FROM Db1.CarTbl1 t1
LEFT OUTER JOIN Db2.CarTbl27...

SQL wouldn't necessarily have to scan the entire table, if the relevant columns happened to be in a nonclus index -- not likely here, but theoretically possible.

Sorry, I couldn't tell you that, I'm not a SAN/hardware person, just a DBA.

(1) Yes, to me, highly impractical.

(2) No real need to make the db read only:

(A) take diff backup (if you feel safer that way);

(B) Switch to SIMPLE recovery;

(C) Issue a...

I would do this the other way around.  That is, rather then deleting 15 years' worth of data, copy the current 2 year's worth of data to another db.  Then,...

But if we have LEFTor RIGHT JOIN as below,is it possible to improve something at this case?

SELECT pr.column1, pr.column2 FROM PlayersTypesAS pr WITH(NOLOCK) INNER JOIN dbo.Players AS p WITH(NOLOCK)...

Perhaps, but I would really expect SQL to generate effectively the same query plan for either code for something this straight forward.

Your code is not really consistent, but here's my best guess:

UPDATE pt 
SET LastModifiedDateTime = @dt
FROM dbo.PlayersTypes AS pt
INNER JOIN @TableTypePlayersRestrictions AS prt ON prt.PlayersRestrictionID = pt.PlayersRestrictionID

I'd try to use differential backups, assuming you have a large amount of free disk space in qa, or could get it easily.

When you do a full backup in prod...

SELECT 
    [freetext],
    CASE WHEN FC_start = 0 THEN '' ELSE RTRIM(SUBSTRING([freetext], FC_start, FC_length)) END AS value
FROM dbo.testdata
CROSS APPLY (
 ...

If you can provide actual sample data -- CREATE TABLE and INSERT statement(s) -- I can provide code to give you the results you want.