SQL Functions

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 the file of a SELECT report
• A HAVING clause.

Want to get certified in SQL! Learn SQL from top SQL experts and excel in your career with intellipaat’s SQL certification.

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 number 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

Analytical 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 in SQL.
• 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.

Come to Intellipaat’s SQL Community if you have more queries on SQL!

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 an identical group of inputs.

Check out the top SQL Interview Questions to learn what is expected from SQL professionals!

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

We have the perfect professional SQL Training Course for you!

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 values. All of them, apart from RAND, is 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 that 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 strings 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

If you want to become a professional SQL expert. Learn and master this technology by enrolling in Intellipaat’s SQL Course in Dublin.

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
• @@PACK_RECEIVED
• @@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
• TOTAL_READ
• fn_virtualfilestats
• PACKET_ERRORS
• IDLE
• CPU_BUSY
• PACK_RECEIVED

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

Master the core concepts of SQL through this SQL Certification in Malaysia and become an expert!

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 process 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
• @@[email protected]@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 functions 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)
• READTEXT (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
• small-money
• 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 forms 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:

• KILL QUERY NOTIFICATION SUBSCRIPTION
• 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
• ADD SIGNATURE
• 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 DEADLOCK_PRIORITY
• 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_PADDING
• 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 the system tables. They cannot be modified in a straight line by simple update and delete terms. The tables may include columns that 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, every data alteration is dedicated and turns 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.

Learn about databases through our Database courses and get to know this diverse field.

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

Crack Top Companies Interviews by learning from our SQL Server Interview Questions.