Sql Server Interview Question: Functions

What is Deterministic and Nondeterministic Functions?
All functions are deterministic or nondeterministic

Deterministic functions always return the same result any time they are called with a specific set of input values.

Nondeterministic functions may return different results each time they are called with a specific set of input values.

Whether a function is deterministic or nondeterministic is called the determinism of the function.

For example, the DATEADD built-in function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed.

Earlier versions of Microsoft® SQL Server™ have no functionality that is dependent on the determinism of functions. In Microsoft SQL Server 2000, nondeterministic functions cannot be specified in two types of Transact-SQL expressions:

An index cannot be created on a computed column if the computed_column_expression references any nondeterministic functions.

A clustered index cannot be created on a view if the view references any nondeterministic functions.

One of the properties SQL Server records for user-defined functions is whether the function is deterministic. A nondeterministic user-defined function cannot be invoked by either a view or computed column if you want to create an index on the view or computed column.

What is Built-in Function Determinism?
You cannot influence the determinism of any built-in function. Each built-in function is deterministic or nondeterministic based on how the function is implemented by Microsoft SQL Server.

All of the aggregate and string built-in functions are deterministic except the string functions CHARINDEX and PATINDEX. For a list of these functions, see Aggregate Functions and String Functions.

These built-in functions from categories of built-in functions other than aggregate and string functions are always deterministic:

ABS DATEDIFF PARSENAME
ACOS DAY POWER
ASIN DEGREES RADIANS
ATAN EXP ROUND
ATN2 FLOOR SIGN
CEILING ISNULL SIN
COALESCE ISNUMERIC SQUARE
COS LOG SQRT
COT LOG10 TAN
DATALENGTH MONTH YEAR
DATEADD NULLIF

These functions are not always deterministic but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

Function Comments
CAST Deterministic unless used with datetime, smalldatetime, or sql_variant.
CONVERT Deterministic unless used with datetime, smalldatetime, or sql_variant. The datetime and smalldatetime data types are deterministic if the style parameter is also specified.

CHECKSUM Deterministic, with the exception of CHECKSUM(*).
ISDATE Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and style is not equal to 0, 100, 9, or 109.
RAND RAND is deterministic only when a seed parameter is specified.

What is non deterministic function?
All of the configuration, cursor, meta data, security, and system statistical functions are nondeterministic. For a list of these functions, see Configuration Functions, Cursor Functions, Meta Data Functions, Security Functions, and System Statistical Functions.

These built-in functions from other categories are always nondeterministic:


@@ERROR FORMATMESSAGE NEWID
@@IDENTITY GETANSINULL PATINDEX
@@ROWCOUNT GETDATE PERMISSIONS
@@TRANCOUNT GetUTCDate SESSION_USER
APP_NAME HOST_ID STATS_DATE
CHARINDEX HOST_NAME SYSTEM_USER
CURRENT_TIMESTAMP IDENT_INCR TEXTPTR
CURRENT_USER IDENT_SEED TEXTVALID
DATENAME IDENTITY USER_NAME


What are the types of Functions ?
The Transact-SQL programming language provides three types of functions:

Rowset functions
Can be used like table references in an SQL statement. For more information about a list of these functions, see Rowset Functions.

Aggregate functions

Operate on a collection of values but return a single, summarizing value. For more information about a list of these functions, see Aggregate Functions.
Scalar functions

Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. This table categorizes the scalar functions.

Function category Explanation

Configuration Functions
Returns information about the current configuration.

Cursor Functions
Returns information about cursors.

Date and Time Functions
Performs an operation on a date and time input value and returns either a string, numeric, or date and time value.

Mathematical Functions
Performs a calculation based on input values provided as parameters to the function, and returns a numeric value.

Metadata Functions
Returns information about the database and database objects.
Security Functions

Returns information about users and roles.
String Functions
Performs an operation on a string (char or varchar) input value and returns a string or numeric value.

System Functions
Performs operations and returns information about values, objects, and settings in Microsoft® SQL Server™.

System Statistical Functions
Returns statistical information about the system.
Text and Image Functions
Performs an operation on a text or image input values or column, and returns information about the value.

What are the 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.


What are the Cursor Functions?
These scalar functions return information about cursors.
@@CURSOR_ROWS
CURSOR_STATUS
@@FETCH_STATUS
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.

What are the 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. For more information about function determinism, see Deterministic and Nondeterministic Functions.

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



What are the 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


What are the Meta Data Functions?
These scalar functions return information about the database and database objects.
COL_LENGTH
fn_listextendedproperty

COL_NAME
FULLTEXTCATALOGPROPERTY

COLUMNPROPERTY
FULLTEXTSERVICEPROPERTY

DATABASEPROPERTY
INDEX_COL

DATABASEPROPERTYEX
INDEXKEY_PROPERTY

DB_ID
INDEXPROPERTY

DB_NAME
OBJECT_ID

FILE_ID
OBJECT_NAME

FILE_NAME
OBJECTPROPERTY

FILEGROUP_ID
@@PROCID

FILEGROUP_NAME
SQL_VARIANT_PROPERTY

FILEGROUPPROPERTY
TYPEPROPERTY

FILEPROPERTY


All meta data functions are nondeterministic. They do not always return the same results every time they are called with a specific set of input values.


What are the 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.


What are the 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


What are the System Functions?
These scalar functions perform operations on and return information about values, objects, and settings in Microsoft® SQL Server™.


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


What are the 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;

What are the 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.
PATINDEX
TEXTPTR
TEXTVALID
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