Don't change the CASTing for the bits in the WHERE clause, leave as is.  Otherwise I believe SQL will default to a different data type which would force a column...

I generated SQL separately for each view, even though that requires a cursor.  In this specific case, I think you may want to be able to control things table by...

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...