Does system_user run multiple times inside of a SQL statement?

I have numerous queries that use system functions inside of our network. This is just a VERY basic representation. Some of the actual places where system functions are located are in stored procedures, views and triggers, some are in simple queries and others are inside of quite complex queries. We are doing a complete system review at this point and this situation was brought up to me:

SELECT TOP(100) p.Id, p.Name,
     CASE WHERE SYSTEM_USER = 'ME' THEN 'It is Me.' ELSE 'It is not Me.' END as IsItMe
FROM dbo.Person p
WHERE 1 = 1

If I'm returning 100 records, at least according to the TOP(100), will the CASE statement be run 100 times or will the Query Analyzer just run it once because it would be the same result for each record returned?

FYI...We are running with SQL Server 2014 just in case there are any differences between versions.

Solution

It depends on the function.

In this specific case of SYSTEM_USER the entire expression CASE WHEN SYSTEM_USER = 'ME' THEN 'It is Me.' ELSE 'It is not Me.' END as IsItMe is evaluated as a runtime constant.

You can see this from

DECLARE @T TABLE(X INT)

SELECT TOP(100)  CASE WHEN SYSTEM_USER = 'ME' THEN 'It is Me.' ELSE 'It is not Me.' END as IsItMe
FROM @T
OPTION (RECOMPILE
, QUERYTRACEON 3604
, QUERYTRACEON 8605 
, QUERYTRACEON 8606
)

The initial tree is

LogOp_Top NoTies

    LogOp_Project COL: Expr1003 

        LogOp_Get TBL: @T @T TableID=-1530594778 TableReferenceID=0 IsRow: COL: IsBaseRow1001 Hints( NOLOCK )

        AncOp_PrjList 

            AncOp_PrjEl COL: Expr1003 

                ScaOp_IIF varchar collate 872468488,Var,Trim,ML=13

                    ScaOp_Comp x_cmpEq

                        ScaOp_Intrinsic suser_sname

                            ScaOp_Const TI(varbinary,Var,Trim,ML=1) XVAR(varbinary,Not Owned,Value=EMPTY)

                        ScaOp_Const TI(nvarchar collate 872468488,Var,Trim,ML=4) XVAR(nvarchar,Owned,Value=Len,Data = (4,7769))

                    ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=9) XVAR(varchar,Not Owned,Value=Len,Data = (9,It is Me.))

                    ScaOp_Const TI(varchar collate 872468488,Var,Trim,ML=13) XVAR(varchar,Not Owned,Value=Len,Data = (13,It is not Me.))

    ScaOp_Const TI(bigint,Null,ML=8) XVAR(bigint,Not Owned,Value=100)

    ScaOp_Const TI(bigint,ML=8) XVAR(bigint,Not Owned,Value=0)

which gets converted early on to

*** Input Tree: ***
        LogOp_Top NoTies

            LogOp_Project COL: Expr1003 

                LogOp_Get TBL: @T @T TableID=-1530594778 TableReferenceID=0 IsRow: COL: IsBaseRow1001 Hints( NOLOCK )

                AncOp_PrjList 

                    AncOp_PrjEl COL: Expr1003 

                        ScaOp_Identifier COL: ConstExpr1004 

            ScaOp_Const TI(bigint,Null,ML=8) XVAR(bigint,Not Owned,Value=100)

            ScaOp_Const TI(bigint,ML=8) XVAR(bigint,Not Owned,Value=0)

With Expr1003 referencing a constant expression