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.
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 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.
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:
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.
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 the proofing of digital signatures.
Symmetric Encryption and Decryption
Signing and Signature Proof
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:
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 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
- 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:
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:
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:
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:
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:
Trigger Functions in SQL (Transact-SQL)
These SQL functions are used for testing certain alterations in the data values. Some of them are:
Collation Functions in SQL (Transact-SQL)
They are used for specifying the collation of the server. They are:
- 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:
- GOTO label
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
- DECLARE CURSOR
- DECLARE @local_variable
They also carry on :
- @@[email protected]@FETCH_STATUS
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)
- 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 :
- Spatial Types
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)
The commands for managing the SQL servers include the following:
- KILL QUERY NOTIFICATION SUBSCRIPTION
- KILL STATS JOB
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
The expressions for the security of the SQL server are:
- EXECUTE AS Clause
- OPEN SYMMETRIC KEY
- 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.
- 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
- SET DEADLOCK_PRIORITY
- SET LOCK_TIMEOUT
- SET CONCAT_NULL_YIELDS_NULL
- SET CURSOR_CLOSE_ON_COMMIT
- SET FIPS_FLAGGER
- SET IDENTITY_INSERT
- SET LANGUAGE
- SET OFFSETS
- SET QUOTED_IDENTIFIER
- 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
- 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 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.
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