SQL Server System Functions: The Basics

Every SQL Server Database programmer needs to be familiar with the System Functions. These range from the sublime (such as @@rowcount or @@identity) to the ridiculous (IsNumeric()) Robert Sheldon provides an overview of the most commonly used of them.

SQL Server provides a set of system functions for working with database objects, settings, and values. You can use the functions to carry out a variety of tasks, such as generating sequential GUIDs, calculating checksums, or returning identity values. You can also perform such operations as viewing connection properties, retrieving information about your transactions, or returning error messages.

In this article, I walk you through a number of examples that demonstrate how to use system functions. Some of the examples rely on the AdventureWorks2014 database, but you can use a different database to run most of the statements, modifying the T-SQL where necessary. I don’t cover every system function, but I touch upon the majority of them. You can find information about all system functions in the MSDN topic System Functions (Transact-SQL).

Identifier functions

The system functions include several for identifying the connecting environment or the data itself. For example, SQL Server provides the NEWSEQUENTIALID function for generating sequential GUIDs, the HOST_ID function for returning the identifier of the connecting computer, and the HOST_NAME function for returning the computer’s name. The following CREATE TABLE statement uses all three functions to provide default values for columns in the dbo.Products table:

Each time the NEWSEQUENTIALID function runs, it creates a GUID greater than any previously created since Windows first started. Sequential GUIDs can result in faster operations than those generated with the NEWID function. If you restart the server, the NEWSEQUENTIALID function will start with a lower value, but still create unique GUIDs in sequential order.

The workstation identifier returned by the HOST_ID function is actually the process ID (PID) of the application on the client computer that is connecting to SQL Server. The HOST_NAME function returns the workstation name provided by the client application, which means the name is not necessarily reliable.

In practice, you probably won’t want to include information such as the host ID and name in the same table as the product information, choosing instead to log this data elsewhere. For example, you could create an INSERT trigger that sends the data to an audit table. For now, however, the example shown here provides a simply way to demonstrate how easy it is to capture this information.

Another system function that falls into the identifier category is @@IDENTITY, which returns the last identity value inserted into a table. For example, suppose we create a stored procedure for adding data to the Products table:

The last step within the procedure is to return the identity value of the inserted row. As a result, when we run the following statement for the first time, it returns the value 101:

We can use the returned value to log our insert operation or perform other tasks that rely on the identity value. In the meantime, let’s retrieve the row of data we just inserted:

As expected, we find that out ProdID value matches the value returned by the EXEC statement:

ProdID

ProdAltID

ProdName

AppPID

Workstation

101

E0AAAB89-79E8-E511-B4C5-001C429524EA

widget1

5764     

WKSTN9263VM

Notice that the row also includes the data generated by the NEWSEQUENTIALID, HOST_ID, and HOST_NAME functions, as well as the product name itself.

As well as @@identity that provides the last identity generated in the current session, we should just mention that there are two other functions; SCOPE_IDENTITY() that provides the last identity value generated in the current session for just the current scope and IDENT_CURRENT(Tablename) that returns the last identity value generated for a particular table .

Data-specific functions

SQL Server’s system functions also include functions for working with specific types of data. For example, you can use the ISNUMERIC function to determine whether an expression is a valid numeric type, and you can use the ISNULL function to replace NULL with a specified value.

The following example creates the Orders table and the InsertOrder stored procedure, which incorporates both functions into its definition:

The ISNUMERIC function tests whether the @ShipToZip input parameter is a valid numeric value. The function returns 1 if the value is valid, otherwise returns 0. Chances are, you’ll want to use a regular expression or some other device to verify the input parameter, in addition to the application verification, but this example is enough to demonstrate the ISNUMERIC function. If the input value is numeric, the INSERT statement will run.

If the statement does run, it also uses the ISNULL function to replace the value of the @SalesPerson input parameter if that value is NULL. For example, suppose we run the following EXEC and SELECT statements:

Because we do not pass in a value for the @SalesPerson parameter, the SELECT statement returns the following results:

OrderID

ShipToZip

SalesPerson

101

90210

online

If we do provide a value for the @SalesPerson parameter:

We get the results shown in the second row of the following table:

OrderID

ShipToZip

SalesPerson

101

90210

online

102

90210

Jeff

Now suppose we pass in a postal code from a district in London, England:

This time the ISNUMERIC function returns a 0, which results in the procedure’s ELSE statement being executed and returning the following results:

As the example demonstrates, the ISNUMERIC function is very simple to use, but know that the function is anything but simple and can return unexpected results. For a discussion of the ISNUMERIC pitfalls, see the Simple-Talk article How to Get SQL Server Data-Conversion Horribly Wrong. The gist of this discussion is that you’re usually better off using the TRY_CONVERT function if you’re running SQL Server 2012 or later:

Another system function you might find useful (or at least entertaining) is GETANSINULL, which returns the database’s default nullability for the current session. For example, the following SELECT statement returns the default nullability for the AdventureWorks2014 database:

In this case, the statement returns 1, which means the database allows NULL values in the current session, unless a column is specifically defined as NOT NULL. As interesting as this might appear, the GETANSINULL function does not seem to be one that is commonly implemented, but I suppose you can use it to check a database’s nullability before carrying out an operation.

SQL Server also provides the CHECKSUM and BINARY_CHECKSUM system functions for returning the checksum value over a row or list of expressions. This can be handy when you want to create a hash index based on one or more columns in a table. Some developers also use the functions to compare tables to determine whether values have changed, but the results are unreliable enough that even the SQL Server documentation warns against using CHECKSUM in this way:

‘However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.’

Microsoft is not as clear about the BINARY_CHECKSUM function in this regard, so let’s dig into both of the functions a bit more.

To begin with, the order of expressions can affect the CHECKSUM outcome, when comparing data values, as can different collations. There is also the issue of the same checksum being returned for different values, as the Microsoft documentation alludes to. Phil Factor provides a great overview of the CHECKSUM dilemma in his article On Comparing Tables in SQL Server. Borrowing from his examples, I created the following T-SQL code to sum up his points:

The example creates a table variable to hold three values and then runs the CHECKSUM and BINARY_CHECKSUM functions against each one, giving us the following results:

StrValue

Chksum

BiChksum

accosted guards

-1099752389

22470672

accorded feasts

-1103946693

22470672

Accorded Feasts

-1103946693

592896016

Notice that CHECKSUM is case insensitive so treats the second and third values the same. On the other hand, BINARY_CHECKSUM is case sensitive, but treats the first two values the same, even though they are clearly different. This appears to have something to do with values that share the same type and byte representation. Unfortunately, SQL Server documentation provides few specifics about the distinction between the two functions, made worse by the fact that their results are difficult to predict.

To further complicate matters, the SQL Server documentation states that CHECKSUM returns “the checksum value” and “is intended for use in building hash indexes,” whereas BINARY_CHECKSUM returns “the binary checksum value” and “can be used to detect changes to a row of a table.” The latter assertion is particularly surprising given that BINARY_CHECKSUM appears less reliable than CHECKSUM, unless case sensitivity is an overriding concern.

Another interesting fact about the SQL Server documentation is that it states that CHECKSUM returns an INT value but fails to state the return type for BINARY_CHECKSUM. Luckily, we can find that out for ourselves:

As the following results show, both functions return an INT value, although we might have expected a more binary-like result for BINARY_CHECKSUM:

StrValue

ChkType

BiChkType

Accorded Feasts

int

int

If you plan to use either of the checksum functions to compare rows, then do so with caution and be sure your application can tolerate a few anomalies; otherwise, go with another solution.

In the meantime, let’s look at how we can use CHECKSUM to create a hash index. In the following T-SQL code, I use the function in the table definition to create a hash on the ProdName column and then create an index on the ProdHash column:

After I create the index, I insert a row into the Product table. I can then use the CHECKSUM function in my WHERE clause to compare a value to the ProdHash column:

As expected, the SELECT statement returns the row I just created, as shown in the following results:

ProdID

ProdName

ProdHash

101

Widget Enterprise 1

422011215

You probably noticed that I preceded my string with N' to pass it in as Unicode. Had I instead run the following SELECT statement, without the N' prefix, the query would have returned no results:

When working with the checksum functions, you want to be sure you take into account all the possible differences and peculiarities. And be sure to check out the HashBytes function. It gives you more control over the algorithm used to hash the values and provides more reliable results, although it is not as fast as the checksum alternatives.

Session-related functions

Several of the system functions available in SQL Server provide session-related information. One of these, CONNECTIONPROPERTY, returns information about the properties associated with the current connection. When you call the function, you must specify one of several acceptable string values. For example, the following SELECT statement uses the function to call the net_transport property:

The net_transport property refers to the physical transport protocol used by the connection. The value returned will be one of the following types: HTTP, Named pipe, Session, Shared memory, SSL, TCP, or VIA.

You can instead call the protocol_type property, which returns either TSQL or SOAP:

The function supports other properties, but you get to the point. Refer to SQL Server online help if you want to see the full list. Note, however, you can retrieve the same information through the sys.dm_exec_connections system view, along with other properties not available through the CONNECTIONPROPERTY function:

In addition to the net_transport and protocol_type values, the SELECT clause includes the session_id and num_reads values, neither of which are available to the CONNECTIONPROPERTY function. The WHERE clause uses the @@SPID built-in function to return the session ID of the current user process, giving us the following results:

session_id

net_transport

protocol_type

num_reads

52

Shared memory

TSQL

17

Another session-related system function is CURRENT_REQUEST_ID, which returns the ID of the current request in the current session. On the surface, there might seem to be few uses for this function, and SQL Server documentation offers even fewer clues, other than to suggest using the function in conjunction with @@SPID.

One possible use of the CURRENT_REQUEST_ID function is for checking your code’s performance from within the code. For example, you can use the function along with @@SPID to query the sys.dm_exec_requests system view multiple times in a single batch:

In this case, we’re using a WAITFOR statement to simulate our primary operation. By querying the sys.dm_exec_requests view multiple times, we can measure the operation’s performance.

SQL Server also supports the CONTEXT_INFO function to return the context_info value that was explicitly set for the current session or batch through the use of a SET CONTEXT_INFO statement, as shown in the following example:

The SET CONTEXT_INFO statement associates a binary data value (up to 128 bytes) with the current session or connection. In SQL Server Management Studio, the SELECT statement returns the following results:

However, you can also retrieve the context_info value by querying the sys.dm_exec_sessions system view:

Not surprisingly, the view will also provide us with an assortment of other session-related information, such as the login time, host name, CPU time, memory usage, and much more. But if you need only the context_info value, the CONTEXT_INFO function is the way to go.

Transactions and statement execution

SQL Server also provides a number of system functions related to transactions and statement execution. For example, you can use the @@ROWCOUNT function to return the number of rows affected by the last statement, whether the statement modified data or simply read that data. The following example shows how you might use the function in a batch to return a message based on the row count:

The T-SQL code updates a single row and then captures the row count. In this case, the batch returns the following message:

Given the we’ve defined the table without a primary key or unique index, any number of rows could have potentially been updated, but this was meant only as a simple example to demonstrate how the @@ROWCOUNT function can be used, not only to return a message, but also for logging and auditing purposes.

Note that, if you plan to carry out operations that exceed 2 billion rows, you should instead use the ROWCOUNT_BIG function.

Another count-like function is @@TRANCOUNT, which returns the number of BEGIN TRANSACTION statements that have run in the current sessions. Let’s take a look at how this works by embedding one transaction in another and returning counts along the way:

We start by retrieving the first transaction count. Then we begin the first transaction and get another count. Next we begin a second transaction and get that count. We commit the second transaction and then the first, getting counts every step of the way. The final SELECT statement returns the following results:

As you can see, the first and last calls to @@TRANCOUNT result in a 0 value, the second and fourth calls result in 1, and the third call results in 2, which is the call embedded in both active transactions. It turns out that the COMMIT statement decrements the count by one, as would the ROLLBACK statement, had we included one.

Now let’s look at the XACT_STATE function, which returns the transaction state of a current request. We’ll start by creating the Products table:

Notice that the table includes a foreign key that references the Production.Product table in the AdventureWorks2014 database.

If we were to now call the XACT_STATE function:

We would receive a value of 0, indicating that there are no active user transactions for the current request.

However, let’s now use the function within a transaction. But first, we’ll run a SET XACT_ABORT statement that sets the XACT_ABORT property to ON. Because we set the property to ON, if our statement raises an error, the entire transaction is terminated and rolled back. This also lets us use the XACT_STATE function to capture the transaction state, as shown in the following example:

When we run the batch, the INSERT statement adds the row of data, and the XACT_STATE function returns a value of 1, indicating that the current request has an active transaction. The transaction will then commit.

If we instead pass the value 5 into our INSERT statement, the statement will result in a foreign key constraint violation and the CATCH block will run. This time, the XACT_STATE function will return a value of -1, indicating that the current request has an active transaction but that an error has occurred.

Now let’s look at a much different system function: @@PACK_RECEIVED. The function returns the number of input packets that SQL Server has read from the network since it was last started. For example, when I ran the following SELECT statement, the function returned a value of 2638:

Note that you can also retrieve this same information, and much more, by using the sp_monitor system stored procedure.

There’s one other interesting function I wanted to point out in this category, MIN_ACTIVE_ROWVERSION, which returns the lowest active rowversion value in the current database. The rowversion data type is often used for version-stamping rows in a table, as shown in the following example:

When the rowversion data type is used, SQL Server automatically assigns a binary number to that row that is unique across the entire database. In conjunction with this, we can use the MIN_ACTIVE_ROWVERSION function to retrieve the next available (active) rowversion value:

When I tried this on my system, I received the following value:

I then verified that this was the next available value by running the following INSERT and SELECT statements:

As expected, this SELECT statement returned the same rowversion value that was returned by the preceding SELECT statement. Because that was the lowest active value, that was the value inserted into my table.

Error-related functions

The final category of system functions that we’ll look at are related to capturing error information. To demonstrate how they work, we can start by once again creating a version of our Products table:

As in an earlier example, we are including a foreign key constraint that references the Production.Product table in the AdventureWorks2014 database. Now let’s re-create our InsertProduct stored procedure, using several error-related system functions:

In this case, we’ve included six error-related functions, with names that should make their purpose self-evident. For example, the ERROR_NUMBER function returns the SQL Server error number, and the ERROR_MESSAGE function returns the error text. Now let’s call the stored procedure, passing in an ID value that we know will raise a foreign key violation error:

Because of the foreign key violation, the CATCH block in our stored procedure runs and returns the following error-related information:

As you can see, the functions make it relatively simple to return the details we want. One important point to note, however, is that each function returns a NULL value if called outside the scope of the CATCH block. Clearly, these functions serve a very specific purpose. For more details about them and other error-related functions, refer to the SQL Server documentation.

SQL Server system functions

Although we have not covered all the system functions here, we’ve looked at most of them, so you should have a good sense of their scope and the types of tasks you can accomplish by using them. Because we covered so many of the functions, we could not go into each one in the detail that might have been warranted, so I again refer you to the SQL Server documentation to help you fill in the blanks.

Systems functions represent only a subset of the built-in functions available in SQL Server. In addition to a wide range of scalar functions, SQL Server also provides rowset, aggregate, and ranking function. System functions represent only the tip of the iceberg, but they can serve as a good starting point for familiarizing yourself with the many types of built-in functions that Microsoft offers.

Tags: , , , ,

  • 20380 views

  • Rate
    [Total: 40    Average: 4.4/5]