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.

NOTE

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 */
SET ROWCOUNT 2

/* 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'

Results:

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

Parameters

Used for

STATS_DATE

Table_id, index_id

Determining the last time statistics were updated

COLLATIONPROPERTY

Collation_name, property

Determining the value of a certain collation property

FN_HELPCOLLATIONS

None

Listing supported collations

FN_SERVERSHAREDDRIVES

None

Listing shared drives in clustered servers

FN_VIRTUALFILESTATS

Database_id, file_id

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

FORMATMESSAGE

Message number, parameter value

Constructing an error message using an existing message in sysmessages table

SERVERPROPERTY

Property name

Getting a property value

SESSIONPROPERTY

Option

Getting a session value for various session options

PERMISSIONS

Object_id, column_name

Returning a bitmap of current users' permissions

PARSENAME

Object_name, object_piece

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


  • + Share This
  • 🔖 Save To Your Account