Appendix B
T-SQL System Variables and Functions
The terms “variable” and “function” are often used interchangeably. SQL Server Books Online documents some variables as though they were functions. However, it is important to note that variables are used in expressions to obtain a value, whereas functions process specific business logic and may return a value. Many functions accept input arguments.
This appendix applies to SQL Server 2008, 2008 R2, and 2012. It is a reference of common T-SQL objects provided as a convenient guide to many functions and variables that may be useful in report queries. It is by no means meant to be a comprehensive reference. For complete details and examples of usage, consult Books Online.
System Global Variables
The system-supplied global variables are organized into the following categories:
- Configuration
- Cursor
- System
- System statistics
Configuration
| Variable | Return Type | Description |
| @@DATEFIRST | tinyint | The system setting for the first day of the week:
1 = Monday 2 = Tuesday 3 = Wednesday 4 = Thursday 5 = Friday 6 = Saturday 7 = Sunday The U.S. default is 7. |
| @@DBTS | varbinary | The last assigned unique TimeStamp value. |
| @@LANGID | smallint | The current language ID for the server:
0 = U.S. English 1 = German 2 = French and so on. |
| @@LANGUAGE | nvarchar | The current language string for the server. Returns the language name in the native language form (us_english, Deutsch, Français, Dansk, Español, Italiano, and so on). |
| @@LOCK_TIMEOUT | int | Locks the time-out setting for the current session in milliseconds (ms). |
| @@MAX_CONNECTIONS | int | The maximum concurrent connections setting for the server. |
| @@MAX_PRECISION | tinyint | The maximum precision setting for the decimal and numeric types. The default is 38 significant digits (the total to the left and right of the decimal point). |
| @@MICROSOFTVERSION | int | 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 (such as 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 | The name of the remote server if executing remote procedures. |
| @@SERVERNAME | nvarchar | The name of the current server. |
| @@SERVICENAME | nvarchar | The name of the Windows service for the current SQL Server instance. |
| @@SPID | 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 4,096 (4 KB). |
| @@VERSION | nvarchar | A text string with detailed information about the current version of SQL Server. This includes the major version, build number, service pack, and copyright information. |
Cursor
| Variable | 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 is unknown, 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: 0 = Normal fetch operation -1 = Fetch past last row or unsuccessful -2 = Fetched row has been removed |
System
| Variable | Return Type | Description |
| @@ERROR | int | The 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 | The value of the most recently generated identity value. This is typically the result of an identity column insert. |
| @@ROWCOUNT | int | The number of rows affected by, or returned by, the last operation. |
| @@TRANCOUNT | int | The number of currently active transactions. Used to determine the number of nested transactions. The maximum number of nested transactions is 11. |
System Statistics
| Variable | Return Type | Description |
| @@CONNECTIONS | int | The total connects that have been opened or attempted since the SQL Server service was last started. |
| @@CPU_BUSY | int | The total time in milliseconds that the server has not been idle since the SQL Server service was last started. |
| @@IDLE | int | The total time in milliseconds that the server has been idle since the SQL Server service was last started. |
| @@IO_BUSY | int | The total time in milliseconds that the server has performed physical disk I/O operations since the SQL Server service was last started. |
| @@PACK_RECEIVED | int | The total number of network packets received by the server since the SQL Server service was last started. |
| @@PACK_SENT | int | The total number of network packets sent by the server since the SQL Server service was last started. |
| @@PACKET_ERRORS | int | The total number of network packet errors that have occurred since the SQL Server service was last started. |
| @@TIMETICKS | int | The number of milliseconds per CPU tick. Each tick takes 1/32nd 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 SQL Server service was last started. |
| @@TOTAL_READ | int | The total number of physical disk reads that have occurred since the SQL Server service was last started. |
| @@TOTAL_WRITE | int | The total number of physical disk writes that have occurred since the SQL Server service was last started. |
System Functions
The system functions are organized into the following categories:
- Aggregation
- Checksum
- Conversion
- Cryptographic
- Cursor
- Date and time
- Error handling
- Image/text
- Mathematical
- Metadata
- Ranking
- Rowset
- Security
- String manipulation
- System
- System statistics
Aggregation
| Function | 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 | The same as COUNT(), but returns a bigint type rather than an int type. |
| GROUPING() | int | Used in conjunction with ROLLUP and CUBE operations in a GROUP BY query. 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, it returns NULL. |
| VAR() | float | Calculates the statistical variance for a range of non-null column values. If all values are NULL, it returns NULL. |
| VARP() | float | Calculates the statistical variance over a population for a range of non-null column values. If all values are NULL, it returns NULL. |
Checksum
| Function | Return Type | Description |
| 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 meaningless but consistently yields the same result for a column or row unless a value changes. String comparisons are case-sensitive. |
| 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 meaningless but consistently yields the same result for a column or row unless a value changes. String comparisons are case-insensitive. |
| 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_CHECKSUM() 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 | Return Type | Description |
| CAST() | Returns a specified type. | Converts a value into a specified data type.
CAST(the_value AS the_type) |
| CONVERT() | Returns a specified type. | Converts (and optionally formats) a value into 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) |
Cryptographic
| Function | Return Type | Description |
| AsymKey_ID() | int | Returns the ID of an asymmetric key. |
| Cert_ID | int | Returns the ID of a certificate. |
| CertProperty() | sql_variant | Returns the value of a specified certificate property. |
| DecryptByAsmKey() | varbinary | Decrypts data with an asymmetric key. |
| DecryptByCert() | varbinary | Decrypts data with a certificate's private key. |
| DecryptByKey() | varbinary | Decrypts data using a symmetric key. |
| DecryptByKeyAutoCert() | varbinary | Decrypts by using a symmetric key that is automatically decrypted with a certificate. |
| DecryptByPassPrase() | varbinary | Decrypts data that was encrypted with a passphrase. |
| EncryptByAsmKey() | varbinary | Encrypts data with an asymmetric key. |
| EncryptByCert() | varbinary | Encrypts data with a certificate's public key. |
| EncryptByKey() | varbinary | Encrypts a string of text using a unique identifier key. |
| EncryptByPassPhrase() | varbinary | Encrypts a string of text using a passphrase. |
| Key_GUID() | uniqueidentifier | Returns the global unique identifier of a named encryption key. |
| Key_ID() | int | Returns the integer ID of a named symmetric key. |
| SignByAsymKey() | varbinary | Applies a digital signature generated by an asymmetrical key to a block of plain text. |
| SignByCert() | varbinary | Applies a digital signature generated by a certificate key to a block of plain text. |
| VerifySignedByAsmKey() | int | Verifies that text signed by an asymmetrical key has not been altered. |
| VerifySignedByCert() | int | Verifies that text signed by a certificate has not been altered. |
Cursor
| Function | 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 and Time
| Function | Return Type | Description |
| CURRENT_TIMESTAMP() | datetime | Returns the current date and time. Is synonymous with the GETDATE() function. It exists for ANSI-SQL compliance. |
| 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 smalldatetime) 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 datepart for a date value. The datepart parameter is the same as the DATEDIFF() interval and includes Year, Quarter, Month, DayOfYear, Day, Hour, Minute, Second, or Millisecond. |
| DATEPART() | int | Similar to DATENAME(). However, it returns the integer value representing the specified datepart for a date value. The datepart parameter is the same as the DATEDIFF() interval and includes Year, Quarter, Month, DayOfYear, 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 (UTC), based on the server's time zone settings. UTC is the same as Greenwich Mean Time (GMT). |
| ISDATE() | int | Returns a flag to indicate whether a specified value is, or is capable of being converted into, a date value. |
| MONTH() | int | Returns the month part for a date as an integer. |
| SWITCHOFFSET() | datetimeoffset (Date) | Returns and/or modifies the UTC offset for a time zone. |
| SYSDATETIME() | datetime | Returns the current database system time stamp. |
| SYSDATETIMEOFFSET() | datetimeoffset (Date) | Returns the current database time offset. |
| SYSUTCDATETIME() | datetime2 | Returns the current database system UTC time stamp. |
| TODATETIMEOFFSET() | datetimeoffset | Modifies the time zone offset for a date and time. |
| YEAR() | int | Returns the year part for a date as an integer. |
Error Handling
| Function | Return Type | Description |
| ERROR_LINE() | int | Returns the line number of the last error when called in a CATCH block. |
| ERROR_MESSAGE() | nvarchar | Returns the full error text for the last error when called in a CATCH block. |
| ERROR_NUMBER() | int | Returns the system- or user-defined error number for the last error when called in a CATCH block. |
| ERROR_PROCEDURE() | nvarchar | Returns the name of the stored procedure or function that raised the last error when called in a CATCH block. |
| ERROR_SEVERITY() | int | Returns the system- or user-defined severity value for the last error when called in a CATCH block. |
| ERROR_STATE() | int | Returns the state number for the last error when called in a CATCH block. |
| XACT_STATE() | smallint | Tests the commitability of the current transaction within a CATCH block. Returns -1 if the transaction is uncommittable. |
Image/Text
| Function | Return Type | Description |
| PATINDEX() | bigint or int (depending on input type) | Returns the character index (first position) for a character string pattern occurring within another character string. Similar to CHARINDEX() but supports wildcards. Returns bigint for varchar(max) and nvarchar(max) type strings; otherwise, returns int. |
| TEXTPTR() | varbinary | Returns a varbinary text pointer handle to be used with the READTEXT(), WRITETEXT(), and UPDATETEXT() functions. Used to perform special operations on text, ntext, and image type column data. |
| TEXTVALID() | int | Verifies a varbinary text pointer value, obtained from the TEXTPTR() function. |
Mathematical
| Function | 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 into degrees. |
| EXP() | float | Returns the natural logarithm raised to a specified exponent. The 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 of 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 into 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 | Return Type | Description |
| ASSEMBLYPROPERTY() | sql_variant | Returns descriptive information about a specified assembly property. |
| 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 SQL 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 filename. |
| FILEGROUP_ID() | int | Returns the ID for a file group name. |
| FILEGROUP_NAME() | nvarchar(128) | Returns the file group name for a file group ID. |
| FILEGROUPPROPERTY() | int | Returns a specified file group property value for a file group name and property name. |
| FILEPROPERTY() | int | Returns a specified file property value for a filename and property name. |
| FILE_NAME() | nvarchar | Returns the filename 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 | Returns 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. |
| OBJECTPROPERTYEX() | sql_variant | Similar to OBJECTPROPERTY() but returns descriptive property values. |
| SCHEMA_ID() | int | Returns the schema ID for a schema name. |
| SCHEMA_NAME() | sysname (nvarchar) | Returns the schema name for a schema ID. |
| SQL_VARIANT_PROPERTY() | sql_variant | Returns the base data type and other information about a sql_variant value. |
| TYPE_ID() | int | Returns the ID for a specified data type name. |
| TYPE_NAME() | sysname | Returns the data type name of a specified type ID. |
| TYPEPROPERTY() | int | Returns information about data type properties. |
Ranking
| Function | Return Type | Description |
| DENSE_RANK() | bigint | 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) | bigint | Returns an evenly distributed ranking value, dividing the result into a finite number of ranked groups. |
| RANK() | bigint | 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() | bigint | Returns a running incremental value based on an ORDER BY clause passed into the function. |
Rowset
| Function | Return Type | Description |
| CONTAINSTABLE() | table | Returns a table object that can be used in a join operation. Each row in this table contains a Key column value, which is the primary key value for qualifying rows of the queried table. This key value is useful for joining the resulting table object back to the physical table to obtain column values. Two arguments are passed: the name of the indexed table and a search string containing words to be matched. |
| FREETEXTTABLE() | table | Similar to CONTAINSTABLE(), but the search condition can match inexact phrasing rather than exact words. |
| OPENDATASOURCE() | table | Used to open an ad hoc connection to a remote OLE DB data source and return a table reference to a database object. Arguments include the name of a registered OLE DB provider, a connection string, and the four-part name of a database object. |
| OPENQUERY() | table | Used to reference an existing linked server and return the results of a query. Arguments include the name of the linked server and a query string. |
| OPENROWSET() | table | Used to connect to a remote OLE DB data source and return the results of a query. Arguments include the name of a registered OLE DB provider, a connection string, and a query string. |
| OPENXML() | table | Transforms an XML document string into a rowset table. The table structure conforms to the standard “edge” table format. The sp_xml_preparedocument system stored procedure must be called first to obtain a document handle ID, which is then passed to this function, along with the document text. |
Security
| Function | Return Type | Description |
| 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. |
| ORIGINAL_LOGIN() | sysname (varchar) | Returns the first user or login name for the first system login in the current session context. |
| SUSER_SID() | varbinary | Returns the security ID for a specified username. |
| SUSER_SNAME() | nvarchar | Returns the username for a specified security ID. |
| USER_ID() | int | Returns a username for a specified user ID. |
String Manipulation
| Function | 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 or nvarchar | Returns the leftmost X characters from a character string. |
| LEN() | int | Returns the length of a character string. |
| LOWER() | varchar or nvarchar | Converts a character string into all lowercase characters. |
| LTRIM() | varchar or nvarchar | 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 or bigint | 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 that they can be passed into an expression. |
| REPLACE() | varchar or nvarchar | Returns a character string with all occurrences of one character or substring replaced with another character or substring. |
| REPLICATE() | varchar or nvarchar | Returns a character string consisting of a specified number of repeated characters. |
| REVERSE() | varchar or nvarchar | Returns a character string with all characters in reverse order. |
| RIGHT() | varchar or nvarchar | Returns a specific number of characters from the rightmost side of a character string. |
| RTRIM() | varchar or nvarchar | Removes trailing spaces from the right side of a character string. |
| SOUNDEX() | varchar | 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 or nvarchar | Converts a character string into all uppercase characters. |
System
| Function | Return Type | Description |
| 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. |
| COLUMNS_UPDATED() | varbinary | Used only within an Insert or Update trigger. Returns a bitmap of modified column flags for the current table. Bytes are left-to-right, with the bits in each byte ordered right-to-left, representing the state (0 = unmodified, 1 = modified) of each column. |
| CURRENT_USER() | sysname (varchar) | Returns the name of the current user. 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 returns the same value as the LEN() function, but for other data types the value may be different. |
| fn_Get_SQL() | table | Returns a table type 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. |
| 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 into, 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 89DE6247-C2E2-42DB-8CE8-A787E505D7EA). This type is often used for primary key values in replicated and semiconnected 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. |
| 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, 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, EngineEdition, InstanceName, IsClustered, IsFullTextInstalled, IsIntegratedSecurityOnly, IsSingleUser, IsSyncWithBackup, LicenseType, MachineName, NumLicenses, ProcessID, ProductLevel, ProductVersion, and ServerName. |
| SESSION_USER | nchar | Returns the current username. The 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. The function is called without parentheses. |
| USER_NAME() | nvarchar | Returns the username for a specified user ID. |
System Statistics
| Function | Return Type | Description |
| sys.dm_io_virtual_file_stats() | table | Returns a table type populated with I/O statistics for database files, including log files. |
| sys.dm_db_index_operational_stats() | table | Returns current I/O, locking, latching, and access method activity for each table or index in the database. |
| sys.dm_db_index_physical_stats() | table | Returns size and fragmentation information for the data and indexes of a specified table or view. |
| sys.dm_db_index_usage_stats() | rowset | Returns counts of different types of index operations and the time each type of operation was last performed. |
| sys.dm_db_missing_index_columns() | table | Returns information about database table columns that are missing an index. |
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.