Using the Built-in Scalar Functions in Microsoft SQL Server

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:

  1. Rowset
    functions
    return value can be used like table references in a SQL queries.
  2. Aggregate
    functions
    operate on a large number of values but return a single final
    value
  3. Ranking
    functions
    return a value that provides a rank value for a row.
  4. 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:

  1. Configuration
    functions
  2. Cryptographic
    functions
  3. Cursor
    functions
  4. Data
    Type functions
  5. Date
    and Time Data type functions
  6. Mathematical functions
  7. Metadata
    functions
  8. ODBC
    scalar functions
  9. Replication functions
  10. Security functions
  11. String
    functions
  12. System functions
  13. System
    Statistical functions
  14. Text
    and image functions
  15. 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
value is generated when table with timestamp column is inserted or updated)

@@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
running. The default SQL server instance will return ‘MSSQLSERVER’

@@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
installed SQL server

@@NESTLEVEL

Returns nesting level of current stored procedure execution on local
serve. It starts from 0 and increases for each nested call.

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
signing)

DecryptByKeyAutoCert

To decrypt using a symmetric key automatically decrypted with a
certificate.

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
table or view

IDENT_CURRENT(name of table or view)

IDENTITY

To insert an identity column in a table inside a SELECT clause with
INTO

IDENTITY(data type) AS ‘columnname’

IDENT_INCR

To get the increment value specified for an identity column in a
table or view

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
not included

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
datetime2(7)

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
date

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.

Latest Articles