To get current SESSION ID:

select @@spid

To get current TRANSACTION ID:

select CURRENT_TRANSACTION_ID()

An exemple UPDATE WITH JOIN in MSSQL

update cc
	set cc.ActivateState=2
 from ClientsCards cc
	left join ClientsCardsHeads cch 
 on cch.CardUid=cc.Uid
	where cc.ActivateState=0 and cch.State=3

An "OFFSET FETCH" is a filter, that allows to miss some number of first rows.

For example:

select field1, field2, field3 
from table1
order by field1, field2
offset 100 rows fetch next 50 rows only;

That example return the rows from 101 to 150, from all rows that ordered by field1 and field2.