Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Digging deeper with T-SQL system functions and language tools

Learn how system functions can be used in T-SQL to view general session and system information along with other types of data in Microsoft SQL Server.

Part one of this series examined how to use system functions to retrieve information about data modifications, server properties and users. This article examines other system functions uses, including how to view session information and use SQL Server language tools to enhance your T-SQL statements.

Viewing session information

In addition to viewing user information with system functions, you can also view information about the current session (which can include the session user). In the following example I use several session-specific functions to retrieve data:

  SESSION_USER AS SessionUser,
  APP_NAME() AS AppName,

The SESSION_USER function returns the current user of the current database within the scope of the current session, while the APP_NAME function returns the name of the application that initiated the current session. The SESSIONPROPERTY function returns the specified SET option value for the current session.

In this case, the SELECT statement includes two SESSIONPROPERTY functions. The first retrieves the value for the ANSI_NULLS setting and the second retrieves the value for the QUOTED_IDENTIFIER setting. The following results show the information returned by the SELECT statement:

SessionUser AppName AnsiNulls QuotedIDs
dbo Microsoft SQL Server Management Studio - Query 1 1

Notice that the current user is dbo and the initiating application is SQL Server Management Studio. For the SET options, a value of 1 indicates that the setting is turned on. If it is turned off the SESSIONPROPERTY function returns a value of 0.

Viewing general information

SQL Server supports a number of system functions that let you retrieve general information about the system. The following SELECT statement demonstrates several of these functions:

  CURRENT_TIMESTAMP AS CurrentTimestamp,
  GETANSINULL() AS DefaulNullability,
  HOST_NAME() AS HostName,
  HOST_ID() AS HostId;

As you would expect, the CURRENT_TIMESTAMP function returns a timestamp that is derived from the operating system and GETANSINULL returns the default nullability for the current database in the current session. Likewise, the HOST_NAME function returns the workstation name and the HOST_ID function returns the process ID (PID) of the application on the client computer that is connecting to SQL Server.

The SELECT statement returns the following results:

CurrentTimestamp DefaultNullability  HostName HostId
2010-08-04 17:33:45.590 1 WINSRV 3896

Some system functions can return multiple results. For example, the fn_helpcollations function returns a list of collations supported by an instance of SQL Server, as shown in the following example:

SELECT name AS CollName
FROM fn_helpcollations()
WHERE name LIKE 'greek_ci_ai%';

Notice that I treat the function as I would a table. I return the name column and limit the data returned to certain Greek collations, as shown in the follow results:


As you can see, only four collations fit the search condition as it’s specified in the WHERE clause. Notice, however, how simple it is to retrieve this information by using the fn_helpcollations function.

Using language tools

To wrap up, let’s look at the functions you can use as tools in your T-SQL statements. These types of functions often help to generate specific kinds of data. For example, the following SELECT statement uses the T-SQL NEWID function to create a unique value called uniqueidentifier:


As you can see from the following results, the statement returns a new value displayed as the uniqueidentifier:


Another function you can use is PARSENAME, which lets you parse the components of a fully qualified object name. The function takes two arguments -- the fully qualified object name and the name component that you want to identify. For example, in the following SELECT statement I use the PARSENAME function to parse the individual components of the Address table name:

  PARSENAME('WinSrv.AdventureWorks2008.Person.Address', 4)
    AS ServerName,
  PARSENAME('WinSrv.AdventureWorks2008.Person.Address', 3)
    AS DbName,
  PARSENAME('WinSrv.AdventureWorks2008.Person.Address', 2)
    AS SchemaName,
  PARSENAME('WinSrv.AdventureWorks2008.Person.Address', 1)
    AS ObjectName;

As the following results show, the function parses each component of the table’s fully qualified name:

ServerName DbName SchemaName ObjectName
WinSrv AdventureWorks2008 Person Address

In the next example, I use the ISDATE and ISNUMERIC functions to verify the data type of specific values:

  ISDATE('2010-08-04 17:33:45.590') AS IsDatetime,
  ISNUMERIC('12ec3') AS IsNumber;

The ISDATE function verifies whether the value passed in as an argument is a legitimate datetime value, and the ISNUMERIC function verifies whether the value passed in as an argument is a legitimate numeric value. If the value is the correct type, the function returns a value of 1, but if the value is not the right type the function returns a value of 0. For the example above, the statement returns the following results:

IsDatetime IsNumber
1 0

As you can see, the first value is a legitimate datetime value, but the second value is not a legitimate numeric value.

Another useful function is the ISNULL function, which returns a specified value if the source value is null. For example, in the following SELECT statement I use ISNULL to return a value of 0 if the SalesQuota value is null:

  FirstName + ' ' + LastName AS FullName,
  ISNULL(SalesQuota, 0) AS SalesQuota
  CountryRegionName = 'United States';

In the source data, three rows have null SalesQuota values – Stephen Jiang, Amy Alberts and Syed Abbas. For each of those rows the ISNULL function returns a SalesQuota value of 0, as shown in the following results:

FullName SalesQuota
Stephen Jiang 0.00
Michael Blythe 300000.00
Linda Mitchell 250000.00
Jillian Carson 250000.00
Pamela Ansman-Wolfe 250000.00
David Campbell 250000.00
Tete Mensa-Annan 300000.00
Amy Alberts 0.00
Shu Ito 250000.00
Syed Abbas 0.00
Tsvi Reiter 300000.00

As the examples above and my previous article demonstrate, system functions are very useful when working with SQL Server data. I have not covered all the functions available to each of these categories, however. System functions also include error-handling functions that are useful when you include TRY…CATCH blocks in your T-SQL code. Again, refer to SQL Server Books Online for a complete list of functions and a description of those functions.

Back to part one – An introductory look at T-SQL system functions

Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at www.rhsheldon.com.

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.