Using OPTION (FORCE ORDER) with user definied functions in SQL Server 2005

Really interesting thing I realised today: Using OPTION (FORCE ORDER) within a UDF, at least in SQL Server 2005 (i haven’t tested in other versions), doesn’t work if you are within the RETURN clause. It does, however, if you aren’t.

For example:

CREATE FUNCTION SchemaName.udfName ()
RETURNS VARCHAR(50)
AS
BEGIN
    RETURN
    (   
        SELECT  t1.Field
        FROM    SchemaName.Table1 t1
        INNER JOIN SchemaName.Table2 t2
            ON t1.Field1=t2.Field2
        INNER JOIN SchemaName.Table3 t3
            ON t2.Field1=t3.Field2
        OPTION (FORCE ORDER)
    )
END

will just return

Msg 156, Level 15, State 1, Procedure udfName, Line 12
Incorrect syntax near the keyword ‘OPTION’.

however,

CREATE FUNCTION SchemaName.udfName ()
RETURNS VARCHAR(50)
AS
BEGIN

    DECLARE @ret VARCHAR(50)
    SELECT  @ret=t1.Field
    FROM    SchemaName.Table1 t1
    INNER JOIN SchemaName.Table2 t2
        ON t1.Field1=t2.Field2
    INNER JOIN SchemaName.Table3 t3
        ON t2.Field1=t3.Field2
    OPTION (FORCE ORDER)
    RETURN
    (   
        @ret
    )
END

will work just fine!