Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 30, 2010

Using the Built-in Scalar Functions in Microsoft SQL Server

By Vipul Patel

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM