Appendix B: System Variables and Functions Reference

Variables and functions are often used interchangeably. SQL Server Books Online documents some variables as if they were functions. However it's important to note that variables return a value whereas functions process specific business logic and many functions accept input arguments. Optional arguments are denoted using square brackets.

System Global Variables

The system-supplied global variables are organized into these categories:

• Configuration

• Cursor

• System

• System Statistics

Configuration

Variable Name	Return Type	Description
@@DATEFIRST	TinyInt	Returns the system setting for the first day of the week. 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday. U.S. default is 7.
@@DBTS	VarBinary	The last assigned unique TimeStamp value.
@@LANGID	SmallInt	The current language ID for the server. (US English = 0, German = 1, French = 2, and so on)
@@LANGUAGE	nVarChar	The current language string for the server. Returns the language name in the native language form (us_english, Deutsch, Franais, Dansk, Espaol, Italiano, and so on).
@@LOCK_TIMEOUT	Int	Lock time-out setting for the current session in milliseconds.
@@MAX_CONNECTIONS	Int	The maximum concurrent connections setting for the server.
@@MAX_PRECISION	TinyInt	The maximum precision setting for decimal and numeric types. Default is 38 significant digits (total to the left and right of the decimal point).
@@MICROSOFTVERSION	Int	Returns an internal tracking number used by product development and support groups at Microsoft.
@@NESTLEVEL	Int	The current number of nested stored procedure or trigger calls. This may be used to limit cascading and/or recursive calls prior to reaching the system limit of 32 recursive calls.
@@OPTIONS	Int	The set of query-processing options for the current user session. Multiple options are combined mathematically using bitwise addition (that is, If SELECT @@OPTIONS & (512 + 8192) > 0 ...). Any combination of option values can be added to determine whether all these options are enabled. Option values: 1 = DISABLE_DEF_CNST_CHK 2 = IMPLICIT_TRANSACTIONS 4 = CURSOR_CLOSE_ON_COMMIT 8 = ANSI_WARNINGS 16 = ANSI_PADDING 32 = ANSI_NULLS 64 = ARITHABORT 128 = ARITHIGNORE 256 = QUOTED_IDENTIFIER 512 = NOCOUNT 1024 = ANSI_NULL_DFLT_ON 2048 = ANSI_NULL_DFLT_OFF 4096 = CONCAT_NULL_YIELDS_NULL 8192 = NUMERIC_ROUNDABORT 16384 = XACT_ABORT
@@REMSERVER	nVarChar	Name of the remote server if executing remote procedures.
@@SERVERNAME	nVarChar	Name of the current server.
@@SERVICENAME	nVarChar	Name of the Windows service for the current SQL Server instance.
'ID	Int	The process/session ID assigned to the current user's connection.
@@TEXTSIZE	Int	The current value of the TEXTSIZE option for a query returning data from a Text, nText, or Image type. The default setting is 4096 (4 K bytes).
@@VERSION	nVarChar	Returns a text string with detailed information about the current version of SQL Server. This includes the major version, build number, sevice pack, and copyright information.

Cursor

Variable Name	Return Type	Description
@@CURSOR_ROWS	Int	The row count for the currently open cursor. Used for explicit cursor processing following an OPEN command. If an asynchronous cursor is opened, the row count will not be known and this variable returns –1.
@@FETCH_STATUS	Int	Used as a flag to indicate whether the open cursor has navigated past the last row (EOF). Status values include:
		0 = Normal fetch operation –1 = Fetch past last row or unsuccessful –2 = Fetched row has been removed

System

Variable Name	Return Type	Description
@@ERROR	Int	Value of the most recent error within the current user session. Error numbers (from the sysmessages table) are used to determine the status of an error condition.
@@IDENTITY	Numeric	Value of the most recently generated identity value. This is typically the result of an identity column insert.
@@ROWCOUNT	Int	Number of rows affected by, or returned by, the last operation.
@@TRANCOUNT	Int	Number of currently active transactions. Used to determine the number of nested transactions. The maximum number of nested transactions is 11.

System Statistical

Variable Name	Return Type	Description
@@CONNECTIONS	Int	The total connects that have been opened or attempted since the SQLServer service was last started.
@@CPU_BUSY	Int	The total time in milliseconds that the server has not been idle since the SQLServer service was last started.
@@IDLE	Int	The total time in milliseconds that the server has been idle since the SQLServer service was last started.
@@IO_BUSY	Int	The total time in milliseconds that the server has performed physical disk I/O operations since the SQLServer service was last started.
@@PACK_RECEIVED	Int	The total number of network packets received by the server since the SQLServer service was last started.
@@PACK_SENT	Int	The total number of network packets sent by the server since the SQLServer service was last started.
@@PACKET_ERRORS	Int	The total number of network packet errors that have occurred since the SQLServer service was last started.
@@TIMETICKS	Int	The number of milliseconds per CPU tick. Each tick takes 1/32 of a second.
@@TOTAL_ERRORS	Int	The total number of disk read/write errors that have occurred, while performing physical disk I/O, since the SQLServer service was last started.
@@TOTAL_READ	Int	The total number of physical disk reads that have occurred since the SQLServer service was last started.
@@TOTAL_WRITE	Int	The total number of physical disk writes that have occurred since the SQLServer service was last started.

System Functions

The system functions are organized into these categories:

• Aggregation

• Checksum

• Conversion

• Cursor

• Date

• Image/Text

• Mathematical

• Metadata

• Ranking

• Security

• System

• System Statistics

Aggregation

Function Name	Return Type	Description
AVG()	(numeric—depends on input)	Calculates the arithmetic average for a range of column values. Internally, this function counts rows and calculates the sum for all non-null values in the column and then divides the sum by the count. Returns the same numeric data type as the column.
COUNT()	Int	Counts all non-null values for a column. The row count is returned using COUNT(*) regardless of null values.
COUNT_BIG()	BigInt	Same as COUNT() but returns a BigInt type rather than an Int type.
GROUPING()	Int	Used in conjunction with ROLLUPand CUBE operations in a GROUPBYquery, this function returns 0 to indicate that it is on a detail row and 1 to indicate a summary row.
MAX()	(numeric or date— depends on input)	Returns the largest value in a range of column values.
MIN()	(numeric or date— depends on input)	Returns the smallest value in a range of column values.
STDEV()	Float	Calculates the standard deviation for a range of non-null column values.
STDEVP()	Float	Calculates the standard deviation over a population for a range of non-null column values.
SUM()	(numeric—depends on input)	Calculates the arithmetic sum for a range of non-null column values. If all values are NULL, returns NULL.
VAR()	Float	Calculates the statistical variance for a range of non-null column values. If all values are NULL, returns NULL.
VARP()	Float	Calculates the statistical variance over a population for a range of non-null column values. If all values are NULL, returns NULL.

Checksum

Function Name	Return Type	Description
CHECKSUM()	Int	Calculates a checksum value for a row or range of column values. This function accepts a single column name, a comma-delimited list of columns, or ‘*’ to use the entire row. Accepts columns of all types except Text, nText, Image, Cursor, and Sql_Variant. The returned value itself is meaning-less but will consistently yield the same result for a column or row unless a value changes. String comparisons are case-insensitive.
BINARY_CHECKSUM()	Int	Calculates a checksum value for a row or range of column values. This function accepts a single column name, a comma-delimited list of columns, or ‘*’ to use the entire row. Accepts columns of all types except Text, nText, Image, Cursor, and Sql_Variant. The returned value itself is meaning-less but will consistently yield the same result for a column or row unless a value changes. String comparisons are case-sensitive.
CHECKSUM_AGG()	Int	Calculates a single checksum value for a range of Int type column values. When applied to the result of the CHECKSUM() or BINARY_CHECK-SUM() functions, returns a scalar (single value) checksum value for the entire range of values. Can be used to detect value changes over a table or range of column values.

Conversion

Function Name	Return Type	Description
CAST()	(returns a specified type)	Converts a value to a specified data type. CAST(the_value AS the_type)
CONVERT()	(returns a specified type)	Converts (and optionally formats) a value to a specified data type. Formatting can be applied to numeric and date types.
		CONVERT(the_type, the_value) or CONVERT(the_type, the_value, format_number)

Cursor

Function Name	Return Type	Description
CURSOR_STATUS()	SmallInt	Returns the status of a previously opened cursor. 1 = Open and populated, 0 = Contains no records, –1 = Closed –2 = no cursor or deallocated, –3 = Doesn't exist

Date

Function Name	Return Type	Description
DATEADD()	DateTime or SmallDateTime (depending on input type)	Returns a date value (DateTime or SmallDateTime) from a date value added by X number of date interval units. Units may be Year, Quarter, Month, DayOfYear, Day, Hour, Minute, Second, or Millisecond.
DATEDIFF()	Int	Returns an integer representing the difference between two date values (DateTime or SmallDate-Time) in specified date interval units. Units may be Year, Quarter, Month, DayOfYear, Day, Hour, Minute, Second, or Millisecond.
DATENAME()	nVarChar	Similar to DATEPART(). Returns a character string representing the specified date part for a date value. Datepart parameter is the same as the DATEDIFF() interval and includes Year, Quarter, Month, Day-OfYear, Day, Hour, Minute, Second, or Millisecond.
DATEPART()	Int	Similar to DATENAME(). Returns an integer representing the specified date part for a date value. Datepart parameter is the same as the DATEDIFF() interval and includes Year, Quarter, Month, Day-OfYear, Day, Hour, Minute, Second, or Millisecond.
DAY()	Int	Returns the day date part for a date as an integer.
GETDATE()	DateTime	Returns the current date and time value.
GETUTCDATE()	DateTime	Returns the current date and time value, for the Universal Time Zone, based on the server's time zone settings. UTC is the same as Greenwich Mean Time (GMT).
MONTH()	Int	Returns the month part for a date as an integer.
YEAR()	Int	Returns the year part for a date as an integer.

Image/Text

Function Name	Return Type	Description
PATINDEX()	Int	Returns the character index (first position) for a character string pattern occurring within another character string. Similar to CHARINDEX() but supports wildcards.
TEXTPTR()	VarBinary	Returns a VarBinary text pointer handle to be used with the READTEXT(), WRITETEXT(), and UPDATETEXT() functions. Used for performing special operations on Text, nText, and Image type column data.
TEXTVALID()	Int	Used to verify a VarBinary text pointer value, obtained from the TEXTPTR() function.

Mathematical

Function Name	Return Type	Description
ABS()	(numeric — same type as input)	Returns the absolute value for a numeric value.
ACOS()	Float	Computes the arccosine (an angle) in radians.
ASIN()	Float	Computes the arcsine (an angle) in radians.
ATAN()	Float	Computes the arctangent (an angle) in radians.
ATN2()	Float	Computes the arctangent of two values in radians.
CEILING()	(numeric — same type as input)	Returns the smallest integer value that is greater than or equal to a number.
COS()	Float	Computes the cosine of an angle in radians.
COT()	Float	Computes the cotangent of an angle in radians.
DEGREES()	(numeric — same type as input)	Converts an angle from radians to degrees.
EXP()	Float	Returns the natural logarithm raised to a specified exponent. Result is in exponential form.
FLOOR()	(numeric — same type as input)	Returns the largest integer value that is less than or equal to a number.
LOG()	Float	Calculates the natural logarithm of a number using base-2 (binary) numbering.
LOG10()	Float	Calculates the natural logarithm of a number using base-10 numbering.
PI()	Float	Returns the value for PI.
POWER()	Float	Raises a value to a specified exponent as FLOAT(the_value, the_exponent).
RADIANS()	(numeric — same type as input)	Converts an angle from degrees to radians.
RAND()	Float	Returns a fractional number based on a randomizing algorithm. Accepts an optional seed value.
ROUND()	(numeric — same type as input)	Rounds a fractional value to a specified precision.
SIGN()	Float	Returns –1 or 1 depending on whether a single argument value is negative or positive.
SIN()	Float	Computes the sine of an angle in radians.
SQRT()	Float	Returns the square root of a value.
SQUARE()	Float	Returns the square (n2) of a value.
TAN()	Float	Computes the tangent of an angle in radians.

Metadata

Function Name	Return Type	Description
COL_LENGTH()	Int	Returns the length of a column from the column name.
COL_NAME()	sysname (nVarChar)	Returns the name of a column from the object ID.
COLUMNPROPERTY()	Int	Returns a flag to indicate the state of a column property.
DATABASEPROPERTY()	Int	This function is maintained for backward compatibility with older SWL Server versions. Returns a flag to indicate the state of a database property.
DATABASEPROPERTYEX()	SqlVariant	Returns a numeric flag or string to indicate the state of a database property.
DB_ID()	SmallInt	Returns the database ID from the database name.
DB_NAME()	nVarChar	Returns the database name from the database ID.
FILE_ID()	SmallInt	Returns the file ID from the file name.
FILE_NAME()	nVarChar	Returns the file name from the file ID.
fn_listextendedproperty()	Table	Returns a table object populated with extended property names and their settings.
FULLTEXTCATALOGPROPERTY()	Int	Returns a flag to indicate the state of a full-text catalog property.
FULLTEXTSERVICEPROPERTY()	Int	Returns a flag to indicate the state of a full-text service property.
INDEX_COL()	nVarChar	Returns the name of a column contained in a specified index, by table, index, and column ID.
INDEXKEY_PROPERTY()	Int	Returns a flag to indicate the state of an index key property.
INDEXPROPERTY()	Int	Returns a flag indicating the state of an index property.
OBJECT_ID()	Int	Returns an object ID from the object name.
OBJECT_NAME()	nChar	Returns an object name from the object ID.
OBJECTPROPERTY()	Int	This function allows you to get property information from several different types of objects. It is advisable to use a function designed to query specific object types, if possible. Returns a flag indicating the state of an object property.

Ranking (SQL Server 2005)

Function Name	Return Type	Description
DENSE_RANK()	Int	Returns a running incremental value based on an ORDER BY clause passed into the function. Doesn't preserve the ordinal position of the row in the list if there are ties.
NTILE(n)	Int	Returns an evenly distributed ranking value, dividing the result into a finite number of ranked groups.
RANK()	Int	Returns a running incremental value based on an ORDER BY clause passed into the function. Preserves the ordinal position of the row in the list with duplicate values for ties followed by subsequent skips.
ROW_NUMBER()	Int	Returns a running incremental value based on an ORDER BY clause passed into the function.
fn_trace_geteventinfo()	Table	Returns a table type populated with event information for a specified trace ID.
fn_trace_getfilterinfo()	Table	Returns a table type populated with information about filters applied to a trace, for a specified trace ID.
fn_trace_getinfo()	Table	Returns a table type populated with trace information for a specified trace ID.
fn_trace_gettable()	Table	Returns a table type populated with file information for a specified trace ID.
HAS_DBACCESS()	Int	Returns a flag indicating whether the current user has access to a specified database.
IS_MEMBER()	Int	Returns a flag indicating whether the current user is a member of a Windows group or SQL Server role.
IS_SRVROLEMEMBER()	Int	Returns a flag indicating whether the current user is a member of a database server role.
SUSER_SID()	VarBinary	Returns the security ID for a specified username.
SUSER_SNAME()	nVarChar	Returns the username for a specified security ID.
USER_ID()	SmallInt	Returns a username for a specified user ID.
fn_trace_geteventinfo()	Table	Returns a table type populated with event information for a specified trace ID.

String Manipulation

Function Name	Return Type	Description
ASCII()	Int	Returns the numeric ASCII character value for a standard character.
CHAR()	Char	Returns the ASCII character for a numeric ASCII character value.
CHARINDEX()	Int	Similar to PATINDEX(), returns the index (character position) of the first occurrence of a character string within another character string.
DIFFERENCE()	Int	Returns the numeric difference between two character strings based on the consensus Soundex values.
LEFT()	VarChar	Returns the left-most X characters from a character string.
LEN()	Int	Returns the length of a character string.
LOWER()	VarChar	Converts a character string to all lowercase characters.
LTRIM()	VarChar	Removes leading spaces from the left side of a character string.
NCHAR()	nChar	Like the CHAR() function, returns the Unicode character for a numeric character value.
PATINDEX()	Int	Returns the index (first character position) for the first occurrence of characters matching a specified pattern within another character string. Wildcard characters may be used.
QUOTENAME()	nVarChar	Returns a character string with square brackets around the input value. Used with SQL Server object names so they can be passed into an expression.
REPLACE()	(character or binary types —depending on input)	Returns a character string with all occurrences of one character or substring replaced with another character or substring.
REPLICATE()	VarChar	Returns a character string consisting of a specified number of repeated characters.
REVERSE()	VarChar	Returns a character string with all characters in reverse order.
RIGHT()	VarChar	Returns a specific number of characters from the right-most side of a character string.
RTRIM()	VarChar	Removes trailing spaces from the right side of a character string.
SOUNDEX()	Char	Returns a four-character alphanumeric string representing the approximate phonetic value of a word, based on the U.S. Census Soundex algorithm.
SPACE()	Char	Returns a character string consisting of a specified number of spaces.
STR()	Char	Returns a character string value that represents a converted numeric data type. Three arguments include the value, the overall length, and the number of decimal positions.
STUFF()	(character or binary types depending on input)	Returns a character string with one string placed into another string at a given position and for a specified length
SUBSTRING()	(character or binary types depending on input)	Returns a portion of a character string from a specified position and for a specified length
UNICODE()	Int	Returns the numeric Unicode character value for a specified character.
UPPER()	VarChar	Converts a character string to all uppercase characters.
APP_NAME()	nVarChar	Each session is associated with an application name, passed to the database server by explicit program code or by the driver or data provider.
COALESCE()	(same type as input)	Returns the first non-null value from a comma-delimited list of expressions.
COLLATIONPROPERTY()	Sql_Variant	Returns the value of a specific property for a specified collation. Properties include CodePage, LCID, and ComparisonStyle.
CURRENT_TIMESTAMP()	DateTime	Returns the current date and time and is synonymous with the GETDATE() function. It exists for ANSI-SQL compliance.
CURRENT_USER()	Sysname (VarChar)	Returns the name of the current user and is synonymous with the USER_NAME() function.
DATALENGTH()	Int	Returns the number of bytes used to store or handle a value. For ANSI string types, this will return the same value as the LEN() function, but for other data types, the value may be different.
ENCRYPT()	VarChar	Returns a hexadecimal value as a character string from a character string value. The result is a oneway encrypted value. This value can be read by the database engine (in the case of an object script definition) but cannot be decrypted.
fn_Get_SQL()	Table	Returns a table populated with the full text of a query based on a process handle. This value is stored in the sysprocesses table referencing a SPID. This function was introduced with SQL Server 2000 SP3.
fn_HelpCollations()	Table	Returns a table type populated with a list of collations supported by the current version of SQL Server.
fn_ServerSharedDrives()	Table	Returns a table type populated with a list of drives shared by the server.
fn_VirtualFileStats()	Table	Returns a table type populated with I/O statistics for database files, including log files.
FORMATMESSAGE()	nVarChar	Returns an error message from the sysmessages table for a specified message number and comma-delimited list of parameters.
GETANSINULL()	Int	Returns the nullability setting for the database, according to the ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF database settings.
HOST_ID()	Char	Returns the workstation ID for the current session.
HOST_NAME()	nChar	Returns the workstation name for the current session.
IDENT_CURRENT()	Sql_Variant	Returns the last identity value generated for a specified table regardless of the session and scope.
IDENT_INCR()	Numeric	Returns the increment value specified in the creation of the last identity column.
IDENT_SEED()	Numeric	Returns the seed value specified in the creation of the last identity column.
IDENTITY()	(same as input)	Used in a SELECT. . . INTO statement to insert an explicitly generated identity value into a column.
ISDATE()	Int	Returns a flag to indicate whether a specified value is, or is capable of being converted to, a date value.
ISNULL()	(same as input)	Determines whether a specified value is null and then returns a provided replacement value.
ISNUMERIC()	Int	Returns a flag to indicate whether a specified value is, or is capable of being converted to, a numeric value.
NEWID()	UniqueIdentifier	Returns a newly generated UniqueIdentifier type value. This is a 128-bit integer, globally unique value, usually expressed as an alphanumeric hexadecimal representation (such as ). This type is often used for primary key values in replicated and semi-connected systems.
NULLIF()	(same as input)	Returns a NULL value when two specified arguments have equivalent values.
PARSENAME()	nChar	Returns a specific part of a four-part object name.
PERMISSIONS()	Int	Returns an integer whose value is a bit-wise map indicating the permission or combination of permissions for the current user on a specified database object.
PWDCONPARE()	Int	Compares an encrypted value with an unencrypted character string value. May be used to compare the encrypted and unencrypted forms of a password to determine whether they match. Returns 1 for a match and 0 for no match.
PWDENCRYPT()	VarChar	Returns the encrypted form of a character string in hexadecimal form. May be used to encrypt a password for storage. This value cannot be decrypted using SQL Server tools or Transact-SQL commands.
ROWCOUNT_BIG()	BigInt	Like the @@ROWCOUNT variable, returns the number of rows either returned by or modified by the last statement. Returns a BigInt type.
SCOPE_IDENTITY()	Sql_Variant	Like the @@IDENTITY variable, this function returns the last Identity value generated but is limited to the current session and scope (stored procedure, batch, or module).
SERVERPROPERTY()	Sql_Variant	Returns a flag indicating the state of a server property. Properties include Collation, Edition, Engine Edition, InstanceName, IsClustered, IsFullTextIn-stalled, IsIntegratedSecurityOnly, IsSingleUser, IsSyncWithBackup, LicenseType, MachineName, NumLicenses, ProcessID, ProductLevel, ProductVersion, and ServerName.
SESSION_USER	nChar	Returns the current username. Function is called without parentheses.
SESSIONPROPERTY()	Sql_Variant	Returns a flag indicating the state of a session property. Properties include ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, and QUOTED_IDENTIFIER.
STATS_DATE()	DateTime	Returns the date that statistics for a specified index were last updated.
SYSTEM_USER	nVarChar	Returns the current username. Function is called without parentheses.
USER_NAME()	nVarChar	Returns the username for a specified User ID.

System Statistical

Function Name	Return Type	Description
fn_virtualfilestats	Table	Returns a table type populated with I/O statistics for database files, including log files.