Flat 10% & upto 50% off + 10% Cashback + Free additional Courses. Hurry up
×
UPTO
50%
OFF!

## Popular Functions in SQL

SQL(Structured Query Language) has got a huge number of functions. This article will give a description most of the SQL Functions.

### Check out this Functions in SQL With Examples video

The SQL functions are as follows:

## Aggregate Functions in SQL (Transact-SQL)

Aggregate SQL functions perform a computation on a group of values and give back a distinct one. Apart from for COUNT, these functions pay no attention to null values. They are commonly utilized with the GROUP BY clause of the SELECT statement. They are all predestined. They give back the equivalent value every time they are called by a precise group of inputs.

Aggregate SQL functions are utilized as expressions as follows:

• The decide on file of a SELECT report
• A HAVING clause.

It offers the subsequent SQL Functions:

• AVG (Transact-SQL) – Returns the average of the inputs. Ignores the Null ones
• CHECKSUM_AGG (Transact-SQL) – Gives back the checksum. Ignores the Null ones. OVER clause may follow.
• COUNT (Transact-SQL) – Returns the quantity of values in a set. Returns int.
• COUNT_BIG (Transact-SQL) – Returns the number of items in a group. Returns bigint.
• GROUPING (Transact-SQL) – Returns 1 if aggregated or 0 if not .
• MAX (Transact-SQL) – Returns the highest term in the statement.
• MIN (Transact-SQL) – Returns the minimum value in the expression.
• STDEV (Transact-SQL) – Returns the statistical standard deviation in the statement
• STDEVP (Transact-SQL) – Returns the statistical standard deviation for the population
• SUM (Transact-SQL) – Returns the sum of all the values
• VAR (Transact-SQL) – Returns the statistical variance
• VARP (Transact-SQL) – Returns The Statistical Variance For The Population

## Analytic Functions in SQL (Transact-SQL)

Analytic SQL functions calculate a collective value depending on a collection of rows. But, different aggregate functions return manifold rows for every collection.

• CUME_DIST (Transact-SQL)  –  Analyses the increasing circulation of a term in a group of inputs
• FIRST_VALUE (Transact-SQL) – Returns the first one in an arranged group
• LAG (Transact-SQL) – Allows data from a preceding line in the equivalent output set devoid of the self-join
• LAST_VALUE (Transact-SQL) – Gives back the last value in an arranged collection of inputs
• LEAD (Transact-SQL) – Accesses information from a succeeding row in the identical outcome group devoid of the self-join.
• PERCENTILE_CONT (Transact-SQL) – Finds out a percentile depending on a nonstop sharing of the column value
• PERCENTILE_DISC (Transact-SQL) – Calculates a certain percentile for sorted values in an entire
• PERCENT_RANK (Transact-SQL) – Computes the relative rank of a row within a group of rows.

## Collation Functions in SQL (Transact-SQL)

Collation SQL Function returns the characteristics of a certain collation in the SQL Server.

• COLLATIONPROPERTY (Transact-SQL) – It returns the characteristics of a certain collation in the SQL Server.
• TERTIARY_WEIGHTS (Transact-SQL) – Gives back a binary string for every value in a non-Unicode string statement

## Configuration Functions in SQL (Transact-SQL)

The subsequent scalar SQL functions give back information regarding the present formation settings:

• DATEFIRST
• LANGUAGE
•  OPTIONS
• VERSION
• DBTS
• MAX_PRECISION
• REMSERVER
• TEXTSIZE
• SERVICENAME
• LANGID
• MAX_CONNECTIONS
• SERVERNAME
• SPID
• NESTLEVEL
• LOCK_TIMEOUT

All of these functions are non-predestined. They always don’t give back equivalent outputs each time they are brought, although with the identical group of inputs.

## Conversion Functions in SQL (Transact-SQL)

The subsequent Conversion SQL functions carry on the casting of data type and their converting.

• CAST and CONVERT (Transact-SQL)
• TRY_PARSE (Transact-SQL)
• TRY_CAST (Transact-SQL)
• PARSE (Transact-SQL)
• TRY_CONVERT (Transact-SQL)

## Cryptographic Functions in SQL (Transact-SQL)

These Cryptographic SQL functions carry out the encryption, decryption, signing, and the proofing of digital signatures.

• Symmetric Encryption and Decryption
• SYMKEYPROPERTY
• ENCRYPTBYKEY
• DECRYPTBYKEYAUTOASYMKEY
• ENCRYPTBYPASSPHRASE
• KEY_GUID
• KEY_ID
• DECRYPTBYKEY
• KEY_NAME
• DECRYPTBYPASSPHRASE
• Asymmetric
• ASYMKEY_ID
• ENCRYPTBYCert
• DECRYPTBYASYMKEY
• ENCRYPTBYASYMKEY
• ASYMKEYPROPERTY
• DECRYPTBYCERT
• Signing and Signature Proof
• SIGNBYCERT
• IS_OBJECTSIGNED
• VERIGYSIGNEDBYCERT
• SIGNBYASYMKEY
• VERIFYSIGNEDBYASMKEY
• Symmetric Decryption with Automatic Key Handling
• DecryptByKeyAutoCert – Encryption Hashing
• HASHBYTES – Copying Certificates
• CERTENCODED (Transact-SQL)
• CERTPRIVATEKEY (Transact-SQL)

## Cursor Functions in SQL (Transact-SQL)

The following scalar Cursor SQL functions return information about cursors:

• CURSOR_ROWS
• FETCH_STATUS
• CURSOR_STATUS

All these SQL functions are also non-predestined. This means they do not every time give back the same outputs

## Data Type Functions in SQL (Transact-SQL)

These SQL functions return data about different data types.

• SQL_VARIANT_PROPERTY (Transact-SQL)
• IDENT_CURRENT (Transact-SQL)
• DATALENGTH (Transact-SQL)
• IDENT_INCR (Transact-SQL)
• IDENTITY (Function) (Transact-SQL)
• IDENT_SEED (Transact-SQL)

## Date and Time Data Types and Functions

These are functions that do the following

• Get System Date and Time
• Get Date and Time Parts
• Get Date and Time Values from Their Parts
• Get Date and Time Difference
• Modify Date and Time Values
• Set or Get Session Format Functions
• Validate Date and Time Values

## JSON Functions in SQL (Transact-SQL)

They are used to confirm or modify JSON text or to take out straightforward or composite values.

• JSON_MODIFY – It modernizes the value of a character in a JSON string
• JSON_VALUE – From the JSON string, it takes out a scalar value
• ISJSON – Tests if a string has a valid JSON.
• JSON_QUERY – Takes out an object from the JSON string.

## Logical Functions in SQL (Transact-SQL)

These SQL functions process logical operations. The two functions are:

• CHOOSE (Transact-SQL)
• IIF (Transact-SQL)

## Mathematical Functions in SQL (Transact-SQL)

These SQL functions do a computation, generally depending on inputs like arguments and numeric value. All of them, apart from RAND, are predestined.

## Metadata Functions in SQL (Transact-SQL)

These SQL functions give data about the different data centers and their objects. Some of these functions are

• FILE_ID
• SCHEMA_ID
• OBJECT_DEFINITION
• APPLOCK_MODE
• COLUMNPROPERTY
• INDEX_COL
• FULLTEXTCATALOGPROPERTY
• SERVERPROPERTY, etc.

## Replication Functions in SQL (Transact-SQL)

These SQL functions output data for administering and maintaining the production of replicas.

## Rowset Functions in SQL (Transact-SQL)

The output of these SQL functions is objects are ones that replace the reference tables. They are always non-predestined. Some of these functions are:

• OPENXML
• OPENDATASOURCE
• OPENROWSET
• OPENQUERY
• OPENJSON

## Security Functions in SQL (Transact-SQL)

These SQL functions are for giving out values which are utilized in monitoring the security issues. Some of these SQL functions are:

• CERTPRIVATEKEY (Transact-SQL)
• DATABASE_PRINCIPAL_ID (Transact-SQL)
• HAS_PERMS_BY_NAME (Transact-SQL)
• SYSTEM_USER (Transact-SQL)
• IS_SRVROLEMEMBER (Transact-SQL)
• PERMISSIONS (Transact-SQL)
• SESSION_USER (Transact-SQL)
• PWDCOMPARE (Transact-SQL)

## String Functions in SQL (Transact-SQL)

The string SQL functions operate only on the string inputs and outputs are either string or a number. They are all predestined. Some of the string functions are:

• ASCII
• CONCAT
• FORMAT
• LOWER
• RTRIM
• UPPER
• SUBSTRING
• STRING_SPLIT
• REPLICATE
• RIGHT
• QUOTENAME
• SPACE
• NCHAR

## System Functions in SQL (Transact-SQL)

They work on and give back data about objects and configurations in the SQL Server. Some of them are:

• ERROR_SEVERITY
• GET_FILESTREAM_TRANSACTION_CONTEXT
• @@ROWCOUNT
• \$PARTITION
• ERROR_PROCEDURE
• FORMATMESSAGE
• @@ERROR
• BINARY_CHECKSUM
•  ISNULL
• CONTEXT_INFO
• NEWSEQUENTIALID
• HOST_NAME
• COMPRESS
• CURRENT_TRANSACTION_ID
• MIN_ACTIVE_ROWVERSION
• CONNECTIONPROPERTY
• CURRENT_REQUEST_ID
• GETANSINULL
• NEWID
• @@TRANCOUNT
• ERROR_STATE
• @@IDENTITY

## System Statistical Functions in SQL (Transact-SQL)

These SQL functions output the statistical data regarding the system. All these SQL functions are non-predestined. Some of these SQL functions are:

• CONNECTIONS
• PACK_SENT
• TOTAL_ERRORS
• IO_BUSY
• TOTAL_WRITE
• TIMETICKS
• fn_virtualfilestats
• PACKET_ERRORS
• IDLE
• CPU_BUSY

## Text and Image Functions in SQL (Transact-SQL)

These SQL functions work on texts and also images and then give outputs on them. They are also non-predestined. Some of them are:

• PATINDEX
• TEXTPTR
• TEXTVALID

## Trigger Functions in SQL (Transact-SQL)

These SQL functions are used for testing certain alterations in the data values. Some of them are:

• COLUMNS_UPDATED
• EVENTDATA
• TRIGGER_NESTLEVEL
• UPDATE()

## Collation Functions in SQL (Transact-SQL)

They are used for specifying the collation of the server. They are:

• COLLATE
• SQL Server Collation Name
• Windows Collation Name
• Collation Precedence (Transact-SQL)

## Control-of-Flow Language Functions in SQL (Transact-SQL)

The keywords used for this function are:

• BEGIN…END
• TRY…CATCH
• WHILE
• GOTO label
• RETURN
• CONTINUE
• IF…ELSE
• THROW
• WAITFOR
• BREAK

## Cursors Functions in SQL (Transact-SQL)

These processes the output set by processing a single row at a single time.They perform operations like:

• CREATE PROCEDURE
• FETCH
• SET
• DECLARE CURSOR
• DELETE
• CLOSE
• DECLARE @local_variable
• UPDATE
• DEALLOCATE
• OPEN

They also carry on :

• sp_describe_cursor_tables
• sp_describe_cursor
• @@CURSOR_ROWS@@FETCH_STATUS
• sp_describe_cursor_columns
• CURSOR_STATUS
• sp_cursor_list

## Data Definition Language (DDL) Statements (Transact-SQL)

They explain the data structures. Some of these SQL nfunctions are:

• ALTER Statements (Transact-SQL)
• UPDATE STATISTICS (Transact-SQL)
• DISABLE TRIGGER (Transact-SQL)
• CREATE Statements (Transact-SQL)
• DROP Statements (Transact-SQL)
• ENABLE TRIGGER (Transact-SQL)
• TRUNCATE TABLE (Transact-SQL)

## Data Manipulation Language (DML) Statements (Transact-SQL)

It is used for working and recovering data by using add, query and remove information. Some of these SQL functions statements are:

• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• BULK INSERT (Transact-SQL)
• WRITETEXT (Transact-SQL)
• SELECT (Transact-SQL)
• DELETE (Transact-SQL)
• MERGE (Transact-SQL)
• UPDATETEXT (Transact-SQL)

## Data Types in SQL (Transact-SQL)

It tells the kind of information any variable or expression carries. Some of the data type SQL functions are :

• bigint
• tinyint
• int
• smallint
• money
• numeric
• decimal
• smallmoney
• bit
• real
• float
• smalldatetime
• time
• datetime2
• datetimeoffset
• datetime
• date
• char
• text
• ntext
• nvarchar
• image
• varbinary
• binary
• table
• Spatial Types
•  uniqueidentifier
• sql_variant
• timestamp
• xml
• hierarchyid

## Expressions in SQL (Transact-SQL)

A collection of a certain number of operators and symbols form an expression. An expression can be a variable or a constant or a column too.

## Language Elements in SQL (Transact-SQL)

The language elements include:

• Slash Star Comment (Transact-SQL)
• CREATE DIAGNOSTICS SESSION (Transact-SQL)
• NULL and UNKNOWN (Transact-SQL)
• (Comment) (Transact-SQL)
• Transactions (SQL Data Warehouse)
• USE (Transact-SQL)

## Management Commands

The commands for managing the SQL servers include the following:

• RECONFIGURE
• CHECKPOINT
• KILL
• KILL STATS JOB
• SHUTDOWN
• DBCC

## Operators in SQL (Transact-SQL)

It tells about the work done on the expressions. The operators of the SQL server are:

• Compound Operators
• Bitwise Operators
• Set Operators
• Logical Operators
• String Concatenation Operator
• Arithmetic Operators
• Comparison Operators
• Unary Operators
• Scope Resolution Operator
• Assignment Operator

## Predicates in SQL (Transact-SQL)

They are used in the search situations where Boolean terms are used mostly in where, from and having clauses. They are:

• IS [NOT] NULL
• FREETEXT
• CONTAINS

## Security Statements

The expressions for the security of the SQL server are:

• EXECUTE AS Clause
• OPEN SYMMETRIC KEY
• SETUSER
• REVERT
• REVOKE
• DENY
• GRANT
• CLOSE SYMMETRIC KEY
• EXECUTE AS
• OPEN MASTER KEY
• CLOSE MASTER KEY

## Service Broker Statements

SQL Server offers the below mentioned Service Broker statements.

• SEND
• BEGIN CONVERSATION TIMER
• END CONVERSATION
• MOVE CONVERSATION
• GET CONVERSATION GROUP
• BEGIN DIALOG CONVERSATION
• GET TRANSMISSION_STATUS

## SET Statements (Transact-SQL)

These functions alter the existing session for taking care of data and some of them are as follows:

• Date and time statements
• SET DATEFIRST
• SET DATEFORMAT
• Locking statements
• SET LOCK_TIMEOUT
• Miscellaneous statements
• SET CONCAT_NULL_YIELDS_NULL
• SET CURSOR_CLOSE_ON_COMMIT
• SET FIPS_FLAGGER
• SET IDENTITY_INSERT
• SET LANGUAGE
• SET OFFSETS
• SET QUOTED_IDENTIFIER
• Transactions statements
• SET IMPLICIT_TRANSACTIONS
• SET REMOTE_PROC_TRANSACTIONS
• SET TRANSACTION ISOLATION LEVEL
• SET XACT_ABORT
• Query Execution Statements
• SET ARITHABORT
• SET ARITHIGNORE
• SET FMTONLY
• SET NOCOUNT
• SET NOEXEC
• SET NUMERIC_ROUNDABORT
• SET PARSEONLY
• SET QUERY_GOVERNOR_COST_LIMIT
• SET ROWCOUNT
• SET TEXTSIZE
• Statistics statements
• SET FORCEPLAN
• SET SHOWPLAN_ALL
• SET SHOWPLAN_TEXT
• SET SHOWPLAN_XML
• SET STATISTICS IO
• SET STATISTICS XML
• SET STATISTICS PROFILE
• SET STATISTICS TIME
• ISO Settings statements
• SET ANSI_DEFAULTS
• SET ANSI_NULL_DFLT_OFF
• SET ANSI_NULL_DFLT_ON
• SET ANSI_NULLS
• SET ANSI_WARNINGS

## SQL Server Utilities Statements

It offers commands acknowledged by sqlcmd,osqland code editor but not Transact-SQL statements. They improve readability and operation. They include:

• (Backslash) (Transact-SQL)
• GO (Transact-SQL)

## System Tables (Transact-SQL)

They explain about the system tables. They cannot be modified in a straight line by simple update and delete terms. The tables may include columns which are documented or which are not.

## System Views (Transact-SQL)

These SQL functions showcase the metadata. And some of the SQL functions are :

• Catalog Views
• Information Schema Views
• Compatibility Views
• Replication Views
• Dynamic Management Views and Functions

## Transaction Statements (Transact-SQL)

A transaction is a solitary piece of job. If it is doing well, all every data alteration are dedicated and turn out to be an everlasting fraction of the database. If a transaction comes across failures and should be rejected, then every data alteration gets deleted.

## Variables (Transact-SQL)

For working on the variables, the functions in SQL are:

• Declare @local_variable
• SET @local_variable
• SELECT @local_variable

## XML Statements (Transact-SQL)

SQL Server offers the below mentioned XML statements.

• WITH XMLNAMESPACES
• xml_schema_namespace
Previous Next

### "0 Responses on SQL Functions - Date, Rank, String, Aggregate Functions in SQL"

100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.

## Sales Offer

• To avail this offer, enroll before 23rd July 2019.
• This offer cannot be combined with any other offer.
• This offer is valid on selected courses only.
• Cashback will be accumulated in Intellipaat e-wallet
• Please use coupon codes mentioned below to avail the offer