How are Scalar UDFs and Co-related Subqueries processed by the Optimizer-CodePudding

I have the following queries. One of them uses scalar UDF in the select statement and another one co-related subquery in the select statement.

Select col1, col2, (select col3 from table2 t2 where t1.col1=t2.col1)
  From table1 t1

Select col1, col2, dbo.getCol3Value(col1)
  From table1 

Create Function dbo.getCol3Value(@col1 as int)
returns int
As
declare @retCol1 int

select @returnCol1 = col3
  from table2
 Where col1= @col1

return @retCol1

I was reading online about how it is not advisable to scalar UDFs since they can cause a lot of performance issues. How are the queries I listed above processed in the background by the SQL server optimizer? What would be a better way of writing these kinds of queries?

Edit: I should have made a few things clear. The subquery or function will only return one value at all times. Also, I need to get the value from table2 for every row in table1.

CodePudding user response：

While T-SQL scalar UDFs that are not inlined have historically been problematic sources of performance problems, there has been recent work from the engineering team to inline some UDFs to make this a non-issue for you. Here are the docs explaining the supported cases: SQL Documentation on UDF Inlining

This academic paper contains more details of the innovation, if you are interested: FROID VLDB Paper