Like this article? We recommend
Like this article? We recommend
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 |