UDFs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive


Hello Friends,<br />I have Scalar UDF. This will return simply desp from the table depends on the Code Id<br /><br />Here the Function Definition:<br /><br />——————————————————-<br />CREATE FUNCTION udf_dscp<br />(<br />@cd [nvarchar](20)<br />)<br />RETURNS [nvarchar](100) AS <br />BEGIN <br /><br />DECLARE @return [nvarchar](100)<br /><br />SELECT @return = [t1].[dscp]<br />FROM [t1] <br />WHERE [t1].[cd] = @cd<br /><br />RETURN @return <br />END<br />———————————————————-<br /><br />Here is the SQL Statement using this function:<br />———————————————<br /><br />SELECT dbo.udf_dscp(type_cd) as [type_dscp] FROM t2 <br /><br />The SQL Statement without using function:<br />—————————————–<br /><br />Select [t1].[dscp] as [type_dscp] <br />FROM t2 <br />INNER JOIN [t1]<br />ON <br />[t1].[cd] = [t2].[type_cd]<br /><br />The Second statement is very fast compare to 1st statement. and It is taking very less reads compare to 1st Statement. In the production database, So many UDFs are like this. So Now I am decided to replace UDFs like this. As already into production, If I replace UDFs like this, Will it be OK, any problem….? As I am new to SQL,I am scaring about this(INNER JOIN).<br />Thanks [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
You should make use of Joins if possible. Madhivanan Failing to plan is Planning to fail