Configuration Functions
These scalar functions return information about current configuration option settings.
| @@DATEFIRST | @@OPTIONS |
| @@DBTS | @@REMSERVER |
| @@LANGID | @@SERVERNAME |
| @@LANGUAGE | @@SERVICENAME |
| @@LOCK_TIMEOUT | @@SPID |
| @@MAX_CONNECTIONS | @@TEXTSIZE |
| @@MAX_PRECISION | @@VERSION |
| @@NESTLEVEL |
All configuration functions are nondeterministic; they do not always return the same results every time they are called with a specific set of input values.unctions.
Cursor Functions
These scalar functions return information about cursors.
All cursor functions are nondeterministic; they do not always return the same results every time they are called with a specific set of input values.unctions.
Date and Time Functions
These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value.
This table lists the date and time functions and their determinism property.unctions.
| Function | Determinism |
|---|---|
| DATEADD | Deterministic |
| DATEDIFF | Deterministic |
| DATENAME | Nondeterministic |
| DATEPART | Deterministic except when used as DATEPART (dw, date). dw, the weekday datepart, depends on the value set by SET DATEFIRST, which sets the first day of the week. |
| DAY | Deterministic |
| GETDATE | Nondeterministic |
| GETUTCDATE | Nondeterministic |
| MONTH | Deterministic |
| YEAR | Deterministic |
Mathematical Functions
These scalar functions perform a calculation, usually based on input values provided as arguments, and return a numeric value.
| ABS | DEGREES | RAND |
| ACOS | EXP | ROUND |
| ASIN | FLOOR | SIGN |
| ATAN | LOG | SIN |
| ATN2 | LOG10 | SQUARE |
| CEILING | PI | SQRT |
| COS | POWER | TAN |
| COT | RADIANS |
Note Arithmetic functions, such as ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS, and SIGN, return a value having the same data type as the input value. Trigonometric and other functions, including EXP, LOG, LOG10, SQUARE, and SQRT, cast their input values to float and return a float value.
All mathematical functions, except for RAND, are deterministic functions; they return the same results each time they are called with a specific set of input values. RAND is deterministic only when a seed parameter is specified.unctions.
Rowset Functions
These rowset functions return an object that can be used in place of a table reference in a Transact-SQL statement.
All rowset functions are nondeterministic; they do not return the same results every time they are called with a specific set of input values.unctions.
Security Functions
These scalar functions return information about users and roles.
| fn_trace_geteventinfo | IS_SRVROLEMEMBER |
| fn_trace_getfilterinfo | SUSER_SID |
| fn_trace_getinfo | SUSER_SNAME |
| fn_trace_gettable | USER_ID |
| HAS_DBACCESS | USER |
| IS_MEMBER |
All security functions are nondeterministic. They do not always return the same results every time they are called with a specific set of input values.unctions.
String Functions
These scalar functions perform an operation on a string input value and return a string or numeric value.
| ASCII | NCHAR | SOUNDEX |
| CHAR | PATINDEX | SPACE |
| CHARINDEX | REPLACE | STR |
| DIFFERENCE | QUOTENAME | STUFF |
| LEFT | REPLICATE | SUBSTRING |
| LEN | REVERSE | UNICODE |
| LOWER | RIGHT | UPPER |
| LTRIM | RTRIM |
All built-in string functions, except for CHARINDEX and PATINDEX, are deterministic. They return the same value any time they are called with a given set of input values.unctions.
System Functions
These scalar functions perform operations on and return information about values, objects, and settings in Microsoft® SQL Server™.
This table lists the system functions and their determinism property.unctions.
| Function | Determinism |
|---|---|
| APP_NAME | Nondeterministic |
| CASE expression | Deterministic |
| CAST and CONVERT | Deterministic unless used with datetime, smalldatetime, or sql_variant. |
| COALESCE | Deterministic |
| COLLATIONPROPERTY | Nondeterministic |
| CURRENT_TIMESTAMP | Nondeterministic |
| CURRENT_USER | Nondeterministic |
| DATALENGTH | Deterministic |
| @@ERROR | Nondeterministic |
| fn_helpcollations | Deterministic |
| fn_servershareddrives | Nondeterministic |
| fn_virtualfilestats | Nondeterministic |
| FORMATMESSAGE | Nondeterministic |
| GETANSINULL | Nondeterministic |
| HOST_ID | Nondeterministic |
| HOST_NAME | Nondeterministic |
| IDENT_CURRENT | Nondeterministic |
| IDENT_INCR | Nondeterministic |
| IDENT_SEED | Nondeterministic |
| @@IDENTITY | Nondeterministic |
| IDENTITY (Function) | Nondeterministic |
| ISDATE | Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and the style parameter is not equal to 0, 100, 9, or 109. Styles 0 and 100 use the default format mon dd yyyy hh:miAM (or PM). Styles 9 and 109 use the default format plus milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM). |
| ISNULL | Deterministic |
| ISNUMERIC | Deterministic |
| NEWID | Nondeterministic |
| NULLIF | Deterministic |
| PARSENAME | Deterministic |
| PERMISSIONS | Nondeterministic |
| @@ROWCOUNT | Nondeterministic |
| ROWCOUNT_BIG | Nondeterministic |
| SCOPE_IDENTITY | Nondeterministic |
| SERVERPROPERTY | Nondeterministic |
| SESSIONPROPERTY | Nondeterministic |
| SESSION_USER | Nondeterministic |
| STATS_DATE | Nondeterministic |
| SYSTEM_USER | Nondeterministic |
| @@TRANCOUNT | Nondeterministic |
| USER_NAME | Nondeterministic |
System Statistical Functions
These scalar functions return statistical information about the system.
| @@CONNECTIONS | @@PACK_RECEIVED |
| @@CPU_BUSY | @@PACK_SENT |
| fn_virtualfilestats | @@TIMETICKS |
| @@IDLE | @@TOTAL_ERRORS |
| @@IO_BUSY | @@TOTAL_READ |
| @@PACKET_ERRORS | @@TOTAL_WRITE |
All system statistical functions are nondeterministic; they do not always return the same results every time they are called with a specific set of input values.unctions.
Text and Image Functions
These scalar functions perform an operation on a text or image input value or column and return information about the value.
These text and image functions are nondeterministic functions and they may not return the same results each time they are called, even with the same set of input values.unctions.
)
| <SITEMAP> <MVC> <ASP> <NET> <DATA> <KIOSK> <FLEX> <SQL> <NOTES> <LINUX> <MONO> <FREEWARE> <DOCS> <ENG> <CHAT ME> <ABOUT ME> < THANKS ME> |