Digging deeper with T-SQL system functions and language tools

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,

    Requires Free Membership to View

    Login

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:

SELECT
  SESSION_USER AS SessionUser,
  APP_NAME() AS AppName,
  SESSIONPROPERTY('ANSI_NULLS') AS AnsiNulls,
  SESSIONPROPERTY('QUOTED_IDENTIFIER') AS QuotedIDs;

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:

SELECT
  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:

CollName
Greek_CI_AI
Greek_CI_AI_WS
Greek_CI_AI_KS
Greek_CI_AI_KS_WS

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:

SELECT NEWID() AS NewGuid;

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

NewGuid
91C81A03-8B5F-4133-BADD-901F5528C8CC

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:

SELECT
  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:

SELECT
  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:

SELECT
  FirstName + ' ' + LastName AS FullName,
  ISNULL(SalesQuota, 0) AS SalesQuota
FROM
  Sales.vSalesPerson
WHERE
  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

ABOUT THE AUTHOR
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.

This was first published in August 2010

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top