Microsoft SQL Server 2012 Bible

Scalar Functions

Scalar functions return a single value. They are commonly used in expressions within the SELECT, WHERE, ORDER BY, GROUP, and HAVING clauses, or T-SQL code. SQL Server includes dozens of functions. This section describes some useful functions.

Best Practice

Performance is as much a part of the data-schema design as it is a part of the query. Plan to store the data in the way that it will be searched by a WHERE condition, rather than depend on manipulating the data with functions at query time. Although using a function in an expression in a result-set column may be unavoidable, using a function in a WHERE condition forces the function to be calculated for every row. In addition, another bottleneck is created because using a function in a WHERE clause makes it impossible for the Query Optimizer to use an index seek — it has to use a scan instead, resulting in much more I/O.

User Information Functions

In a client/server environment, it's good to know who the client is. Toward that end, the following four functions are useful, especially for gathering audit information:

• USER_NAME(): Returns the name of the current user as he or she is known to the database. When a user is granted access to a database, a username that is different from the server login name may be assigned. The results are affected by an EXECUTE AS command, in which case the username shown is that of the impersonated user.
• SUSER_SNAME(): Returns the login name by which the user was authenticated ...