SQL
server provides an assortment of functions that database developers can use in their
application implementation. This article provides an insight to the scalar
functions, which come in the box with your SQL server instance.
Introduction
A lot of built-in functions come with SQL server, which can
be used in SQL queries. These functions can be divided into four major
categories:
- Rowset
functions return value can be used like table references in a SQL queries. - Aggregate
functions operate on a large number of values but return a single final
value - Ranking
functions return a value that provides a rank value for a row. - Scalar
functions are defined as functions that operate on a single value and
return a single value. They are used where an expression would be valid.
Let us examine the built-in scalar functions in SQL Server.
Types of Scalar functions
Scalar functions can be categorized into the following:
- Configuration
functions - Cryptographic
functions - Cursor
functions - Data
Type functions - Date
and Time Data type functions - Mathematical functions
- Metadata
functions - ODBC
scalar functions - Replication functions
- Security functions
- String
functions - System functions
- System
Statistical functions - Text
and image functions - Trigger
functions
Let us look at a few categories of the scalar functions.
Configuration functions
These functions return information about the current
configuration. Due to their nature, they are non-deterministic.
|
Function |
Purpose |
|
@@DATEFIRST |
Returns the first day of the week |
|
@@OPTIONS |
Returns the current SET options for the session |
|
@@DBTS |
Returns last used timestamp value of the database. (A new timestamp |
|
@@REMSERVER |
Deprecated (returns name of SQL server) |
|
@@LANGID |
Returns language ID of the language being used |
|
@@SERVERNAME |
Returns name of local server |
|
@@LANGUAGE |
Returns name of language being used |
|
@@SERVICENAME |
Returns name of registry key under which SQL server instance is |
|
@@LOCK_TIMEOUT |
Returns lock timeout value in milliseconds for the current session |
|
@@SPID |
Returns session ID for current user process |
|
@@MAX_CONNECTIONS |
Returns maximum number of simultaneous user connections allowed |
|
@@TEXTSIZE |
Returns current value of TEXTSIZE option |
|
@@MAX_PRECISION |
Returns precision value used in decimal and numeric data types |
|
@@VERSION |
Returns version, processor, build date and operating system for the |
|
@@NESTLEVEL |
Returns nesting level of current stored procedure execution on local |
Cryptographic functions
They are used for supporting encryption, decryption, digital
signing and their validation.
|
Function |
Purpose |
|
EncryptByKey |
For encrypting data using symmetric key EncryptByKey(GUID_Key, @cleartext) |
|
DecryptByKey |
For decrypting data using symmetric key DecryptByKey(GUID_Key, @ciphertext) |
|
EncryptByPassPhrase |
For encrypting data with a passphrase |
|
DecryptByPassPhrase |
For decrypting data which was encrypted with a passphrase |
|
Key_ID |
For getting the symmetric key ID of the database |
|
Key_GUID |
For getting the symmetric key GUID of the database |
|
EncryptByAsmKey |
For encrypting data with asymmetric key |
|
DecryptByAsmKey |
For decrypting data with asymmetric key |
|
EncryptByCert |
For encrypting data with public key of certificate |
|
DecryptByCert |
For decrypting data with private key of certificate |
|
Cert_ID |
For getting the certificate ID. |
|
AsymKey_ID |
For getting the asymmetric key ID |
|
CertProperty |
For getting the value of a specific certificate property |
|
SignByAsymKey |
For signing plaintext with asymmetric key |
|
VerifySignedByAsymKey |
To test integrity of digitally signed data |
|
SignByCert |
To sign text with certificate and return signature. |
|
VerifySignedByCert |
To test integrity of digitally signed data (using Certificate for |
|
DecryptByKeyAutoCert |
To decrypt using a symmetric key automatically decrypted with a |
Cursor functions
Cursor functions return information about cursors and hence
are non-deterministic.
|
Function |
Purpose |
|
@@CURSOR_ROWS |
It returns number of rows in last cursor opened in the SQL connection |
|
CURSOR_STATUS |
To determine whether a store procedure has returned a cursor or not |
|
@@FETCH_STATUS |
To get the status of the last cursor FETCH statement. |
Data type functions
Data type functions return data about identity values and
other data type values. These functions are not deterministic.
|
Function |
Purpose |
Usage |
|
DATALENGTH |
To get the number of bytes of a expression |
DATALENGTH (expression) |
|
IDENT_SEED |
To get the original seed value used for a identity column |
IDENT_SEED(name of table or view) |
|
IDENT_CURRENT |
To get the last seed value generated for an identity column in a |
IDENT_CURRENT(name of table or view) |
|
IDENTITY |
To insert an identity column in a table inside a SELECT clause with |
IDENTITY(data type) AS ‘columnname’ |
|
IDENT_INCR |
To get the increment value specified for an identity column in a |
IDENT_INCR(name of table or view) |
|
SQL_VARIANT_PROPERTY |
To get the base data type about a sql_variant value |
SQL_VARIANT_PROPERTY(sql_variant expression, property) |
Date and Time Data Functions
Date and Time Data functions are used to perform
calculations on date and time input values. These are not deterministic.
|
Function |
Purpose |
Usage |
|
SYSDATETIME |
To get the system time as a datetime2(7) value . Timezone offset is |
SYSDATETIME() |
|
SYSDATETIMEOFFSET |
Same as above with timezone offset included. The return type is datetimeoffset(7) |
SYSDATETIMEOFFSET() |
|
SYSUTCDATETIME |
Same as above with time returned as UTC time. The type is |
SYSUTCDATETIME() |
|
CURRENT_TIMESTAMP |
To get the current time as datetime2(7) |
CURRENT_TIMESTAMP |
|
GETDATE |
Gets the date as datetime2(7). Time zone offset not included |
GETDATE() |
|
GETUTCDATE |
Gets date as datetime2(7) with time as UTC time. |
GETUTCDATE() |
|
DATENAME |
To get a string representation of a specified datepart of a given |
DATENAME(datepart, date) |
|
DATEPART |
To get an integer of a specified datepart of a given date |
DATEPART(datepart, date) |
|
DAY |
Gets the day part of a given date as an integer |
DAY(date) |
|
MONTH |
Gets the month part of a given date as an integer |
MONTH(date) |
|
YEAR |
Gets the year part of a given date as an integer |
YEAR(date) |
|
DATEDIFF |
To get the time different between two dates as dateparts |
DATEDIFF(datepart, start, end) |
|
DATEADD |
To get a new date by adding a datepart to a date |
DATEADD(datepart, number, date) |
|
SET DATEFIRST |
To set the first day of a week |
SETD ATEFIRST <number> |
|
ISDATE |
Checks whether the expression is a valid date or not |
ISDATE (expression) |
Mathematical functions
Mathematical functions are used to perform mathematical
calculations on input values. These functions are deterministic.
|
Function |
Purpose |
|
ABS |
Returns the absolute value of specified expression |
|
ACOS |
Returns the angle whose cosine value is the expression |
|
ASIN |
Returns the angle whose sine value is the expression |
|
ATAN |
Returns the angle whose tangent value is the expression |
|
ATN2 |
To get the angle between two specified points |
|
CEILING |
To get the smallest integer greater than the specified expression |
|
COS |
Gets the cosine value of the given angle |
|
COT |
Gets the cotangent value of the given angle |
|
DEGREES |
Gets the angle in degrees when input is specified in radians |
|
EXP |
To get the exponential value of the expression provided |
|
FLOOR |
To get the largest integer lesser than the specified expression |
|
LOG |
To get natural log value of the specified expression |
|
LOG10 |
To get base-10 log value of the specified expression |
|
PI |
To get the value of π |
|
POWER |
Gets the value of provided expression to the specified power |
|
RADIANS |
Gets the value in radians when input is provided in degrees |
|
RAND |
Returns a random float value between 0 and 1 |
|
ROUND |
Gets a numeric value rounded to the specified precision |
|
SIGN |
Gets the sign of a specified expression |
|
SIN |
Gets the sine value of specified angle in radians |
|
SQRT |
Gets the square root of the specified value |
|
SQUARE |
Gets the square of the specified value |
|
TAN |
Gets the tangent value of the specified angle in radians. |
There are even more types of the scalar functions, which we
have not discussed here. You can check the other functions on MSDN.
Summary
We have seen a lot of built-in functions that come with a
SQL Server install. Database developers should use these functions for their
applications without reinventing the wheel.
