Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Other System Functions

The CURRENT_TIMESTAMP function works exactly the same way as GETDATE(): It returns current date and time. Oddly enough, CURRENT_TIMESTAMP is classified as a system function, whereas GETDATE falls into the date and time category.

The DATALENGTH function is similar to the LEN function, which returns the length of a particular string expression. DATALENGTH returns the number of bytes used to represent an expression of any data type.

The @@TRANCOUNT function returns the number of open transactions on a particular connection. If you experience locking problems or SQL Server complains about transactions being opened, you can check the value of @@TRANCOUNT to troubleshoot such issues. @@TRANCOUNT can also be used for error handling; if @@TRANCOUNT returns anything other than 0, something must have gone wrong and you have uncommitted transactions. (Refer to my article about transactions and locking for more information on this topic.)

The @@ERROR function returns the number of the last error encountered on the current connection. If there are no errors, @@ERROR returns 0. Not surprisingly, @@ERROR is used for error handling.

The @@ROWCOUNT function returns the number of rows affected by your last query. This function can be used effectively to find out whether the number of rows modified is the same as what you intended to modify. If the two values are different, something went wrong.

The ROWCOUNT_BIG() function does the same thing as @@ROWCOUNT, but returns a BIGINT instead of an INT data type.


It's easy to confuse the functionality of ROWCOUNT and @@ROWCOUNT. The former advises SQL Server to affect only a specified number of rows (similar to the TOP keyword); the latter simply counts the number of rows affected, as shown here:

/* first limit the output to 2 rows */

/* this query will affect only 2 rows */
SELECT title_id, title FROM titles

/* now use functions to count the number of 
affected rows */
SELECT @@ROWCOUNT AS '@@rowcount_output', 
       ROWCOUNT_BIG() AS 'rowcount_big_output'


title_id       title                                                                            
--------     --------------------------------------------------------------- 
PC1035       But Is It User Friendly?
PS1372       Computer Phobic AND Non-Phobic Individuals: Behavior Variations

@@rowcount_output     rowcount_big_output  
-----------------     -------------------- 
2                     2

The APP_NAME() function returns a string with the name of the application that initiated the database connection. APP_NAME() can be helpful if you're troubleshooting a connection and want to know which app initiated the offending process.

The rest of the system functions are limited to retrieving specific data about SQL Server. The following table provides a quick reference for what the functions do:

System Function


Used for


Table_id, index_id

Determining the last time statistics were updated


Collation_name, property

Determining the value of a certain collation property



Listing supported collations



Listing shared drives in clustered servers


Database_id, file_id

Determining I/O stats for a certain database or log file


Message number, parameter value

Constructing an error message using an existing message in sysmessages table


Property name

Getting a property value



Getting a session value for various session options


Object_id, column_name

Returning a bitmap of current users' permissions


Object_name, object_piece

Returning server, owner, database or name portion of the specified object

  • + Share This
  • 🔖 Save To Your Account