How does the transaction log work in SQL Server when i run a DML I would like to ask you something about a query execution in SQL. in general, I know that when I run a SELECT query I mean a view, this is loaded in cache memory then is parsed, compiled and then the query optimizer applies improvements based on execution plans so for that we have also the support coming from buffer pool (storage engine and relation engine). So, all that happens in memory and finally the result set is sent to SQL client.

Otherwise, we have the log file in a DB and here is my doubt because I don’t know if the log file in SQL is also used in queries ‘SELECT’ executions. Or it’s just used in transactions like an update, delete or insert so I really would like to know the role of transactional log in SQL when I perform a query (view) and also his role in the other DML transactions.
Yes, SELECT statements can in fact use space in the tempdb transaction log, as temporary working space to complete the query:

The Transaction Log (SQL Server):


  Long-running transactions might also fill up tempdb's transaction log. Tempdb is used implicitly by user transactions for internal objects such as work tables for sorting, work files for hashing, cursor work tables, and row versioning. Even if the user transaction includes only reading data (SELECT queries), internal objects may be created and used under user transactions. Then the tempdb transaction log can be filled.