An introductory look at T-SQL system functions

SQL Server includes built-in functions that let you perform a variety of tasks, such as converting data, working with string values and performing mathematical calculations. Some SQL Server functions are categorized as system functions, which let you view information about update and insert operations, retrieve server property settings and access details about the current session and its users. You can also use system functions to perform a number of language-related tasks.

This article will introduce you to many of the system functions included in SQL Server. Although I won’t cover every function, I’ll describe as many as possible and look at several different types in order to give you a broad foundation. For a complete list of system functions and details about each one see the topic “System Functions (Transact-SQL)” in SQL Server Books Online.

Note:There are examples in this article that I ran within the context of the AdventureWorks2008 sample database installed in a local instance of SQL Server 2008. You can also run the examples with the AdventureWorks database in SQL Server 2005, but be sure to change the database name as necessary. Also, if you run the examples on SQL Server 2005, some of your results will be slightly different from what is shown here.

Viewing information about data modifications

Some of the system functions supported by SQL Server let you return details about your update and insert operations. For example, you can retrieve the last identity value inserted into a table, or the number of rows updated or added to a table. To demonstrate how these functions work I defined the following table and populated it with several rows:

USE AdventureWorks2008;

IF OBJECT_ID('TableA', 'U') IS NOT NULL
DROP TABLE dbo.TableA;

CREATE TABLE dbo.TableA
(
  ColA INT IDENTITY(101, 100) NOT NULL,
  ColB VARCHAR(20) NOT NULL
);

INSERT INTO TableA (ColB) VALUES('red');
INSERT INTO TableA (ColB) VALUES('blue');
INSERT INTO TableA (ColB) VALUES('green');
INSERT INTO TableA (ColB) VALUES('yellow');
INSERT INTO TableA (ColB) VALUES('orange');

SELECT * FROM TableA;

Below is a very simple table that’s been populated with five rows. Notice that the ColA column is defined as an identity column with a seed value of 101 and an increment value of 100. The final SELECT statement produces the following results:

Now let’s look at an example with several system functions that retrieve information about the data just inserted into the table. The following SELECT statement includes five functions, three related to the identity value and two related to row counts:

SELECT
  IDENT_CURRENT('TableA') AS LastValue,
  IDENT_SEED('TableA') AS SeedValue,
  IDENT_INCR('TableA') AS IncrValue,
  @@ROWCOUNT AS RowsAffected,
  ROWCOUNT_BIG() AS BigRowsAffected;

The first function, IDENT_CURRENT, retrieves the last identity value inserted into TableA. As you can see, TableA is specified as an argument to the function. The function returns the information without regard to the session it was added to the table or the scope of the statement in which it was added. This is important because SQL Server supports other identity-related functions that are specific to the current session (@@IDENTITY) and the current scope and session (SCOPE_IDENTITY).

The next two functions in the example above return the setting information for the identity column of the specified table. The IDENT_SEED function returns the seed value and the IDENT_INCR function returns the increments value. The following result set includes the values returned by the three identity-related functions:

As you would expect, the last identity value inserted into TableA is 501, the seed value is 101 and the increment value is 100.

Now let’s look at the next two functions in the example. The @@ROWCOUNT function returns the number of rows affected by the last statement. The ROWCOUNT_BIG function does the same thing, except the returned type is bigint. You should use this function if you anticipate that the number of affected rows will be greater than 2 billion.

Notice that neither function is associated with a table because the two functions return data based on the last statement executed. That means if 200 rows have been inserted into a table, a value of 200 will be returned by the functions. If a SELECT statement returns 30 rows, the functions will return a value of 30. In the example above, both functions return a value of 5 because the last statement to run was a SELECT statement that returned five rows.

Viewing server properties

At times, you might want to retrieve the value of a particular server property in which case you can use the SERVERPROPERTY function. The function takes one argument – the name of the property – as shown in the following example:

SELECT
  SERVERPROPERTY('Edition') AS SrvEdition,
  SERVERPROPERTY('InstanceName') AS SrvInstance,
  SERVERPROPERTY('Collation') AS SrvCollation;

In this SELECT statement, I retrieved the settings for the Edition, InstanceName and Collation properties, which are passed in as arguments to the functions. The SELECT statement returns the following results:

Your results might be different, particularly for the first two properties, but you get the point. The function lets you simply retrieve these settings.

Note:When working with a function where you must pass a specific property as an argument, refer to that function’s topic in SQL Server Books Online for a list of properties.

Another type of property setting you can retrieve is associated with collation properties. The function you use in this case is COLLATIONPROPERTY, as shown in the following example:

SELECT
  COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'Version')
    AS CollVersion,
  COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'CodePage')
    AS CollPage;

As you can see, the COLLATIONPROPERTY function takes two arguments -- the name of the collation and the specific property you want to view. In this case, the name of the collation is SQL_Latin1_General_CP1_CI_AS and the properties are Version and CodePage. The settings for both of these properties are shown in the following results:

Notice that the Version value is 0 and the CodePage value is 1252. If I were to plug in a different collation, my results would be specific to that collation.

Viewing user information

SQL Server supports several functions that are specific to the database user and that user’s identification numbers. The following example uses several of these functions to retrieve user information:

SELECT
  USER_NAME() AS UserName,
  USER_ID() AS UserId,
  SUSER_SID() AS UserSid,
  SYSTEM_USER AS SystemUser;

The functions are very straightforward; the USER_NAME function returns the name of the current database user and the USER_ID function returns the database ID for the current user. Additionally, the SUSER_SID function returns the security database ID for the current user, while the SYSTEM_USER function returns the current login. The following results show the data returned by the SELECT statement:

The results themselves are self-explanatory, but note that you can pass in arguments to each of the first three functions in the example. For instance, you can pass in the database user ID to the USER_NAME function to return that user name. You can also pass in the user name as an argument to the USER_ID function to get that user’s ID. Similarly, you can pass a user’s login into the SUSER_SID function to retrieve that user name.

The following example demonstrates how to use these functions in this way:

SELECT
  USER_NAME(1) AS UserName,
  USER_ID('dbo') AS UserId,
  SUSER_SID('WINSRV\Administrator') AS UserSid;

The statement returns the following results:

As you can see, the functions are versatile enough to work in different situations, depending on your needs.

Continue to part two – Digging deeper with system functions and language tools

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.