191
Chapter 8: Data Types, Expressions, and Scalar Functions
8
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 differ-
ent 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 imperson-
ated user.
■ (^) SUSER_SNAME(): Returns the login name by which the user was authenticated to
SQL Server. If the user was authenticated as a member of a Windows user group,
then this function still returns the user’s Windows login name. The results are
affected by an EXECUTE AS command, in which case the username shown is that
of the impersonated user.
■ (^) HOST_NAME(): Returns the name of the user’s workstation.
■ (^) APP_NAME(): Returns the name of the application (if set by the application itself)
connected to SQL Server, as follows:
SELECT
USER_NAME() AS 'User',
SUSER_SNAME() AS 'Login',
HOST_NAME() AS 'Workstation',
APP_NAME() AS 'Application';
Result:
User Login Workstation Application
Dbo Demo\Administrator WIN-V7B3M53ERC1 Management Studio
Date and Time Functions
Databases must often work with date and time data, and SQL Server includes several useful
functions for that. SQL Server stores both the data and the time in a single data type. It
also has types for date only, time only, and zone-aware times.
c08.indd 191c08.indd 191 7/30/2012 4:21:14 PM7/30/2012 4:21:14 PM
http://www.it-ebooks.info