bing
Flat 10% & upto 50% off + Free additional Courses. Hurry up!

SQL Functions

SQL(Structured Query Language) has got a huge number of functions. This article will give a description of them. The SQL functions are as follows:

  1. Aggregate Functions (Transact-SQL)

Aggregate 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 functions are utilized as expressions as follows:
• The decide on file of a SELECT report
• A HAVING clause.

It Offers  The Subsequent 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

2. Analytic Functions (Transact-SQL)

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

3. Collation Functions (Transact-SQL)

It 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

4. Configuration Functions (Transact-SQL)

The subsequent scalar 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.

5. Conversion Functions (Transact-SQL)

The subsequent 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)

6. Cryptographic Functions (Transact-SQL)

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

7. Cursor Functions (Transact-SQL)

The following scalar functions return information about cursors:
• CURSOR_ROWS
• FETCH_STATUS
• CURSOR_STATUS

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

8. Data Type Functions (Transact-SQL)

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

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

10. JSON Functions (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.

11. Logical Functions (Transact-SQL)

These functions process logical operations. The two functions are:
• CHOOSE (Transact-SQL)
• IIF (Transact-SQL)

12. Mathematical Functions (Transact-SQL)

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

13. Metadata Functions (Transact-SQL)

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

14. Replication Functions (Transact-SQL)

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

15. Rowset Functions (Transact-SQL)

The output of these 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

16. Security Functions (Transact-SQL)

These functions are for giving out values which are utilized in monitoring the security issues. Some of these 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)

17. String Functions (Transact-SQL)

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

18. System Functions (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

19. System Statistical Functions (Transact-SQL)

These functions output the statistical data regarding the system. All these functions are non-predestined. Some of these functions are:
• CONNECTIONS
• PACK_SENT
• TOTAL_ERRORS
• IO_BUSY
• TOTAL_WRITE
• TIMETICKS
• TOTAL_READ
• fn_virtualfilestats
• PACKET_ERRORS
• IDLE
• CPU_BUSY
• PACK_RECEIVED

20. Text and Image Functions (Transact-SQL)

These 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

21. Trigger Functions (Transact-SQL)

These functions are used for testing certain alterations in the data values. Some of them are:
• COLUMNS_UPDATED
• EVENTDATA
• TRIGGER_NESTLEVEL
• UPDATE()

22. Collation (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)

23. Control-of-Flow Language (Transact-SQL)

The keywords used for this function are:
• BEGIN…END
• TRY…CATCH
• WHILE
• GOTO label
• RETURN
• CONTINUE
• IF…ELSE
• THROW
• WAITFOR
• BREAK

24. Cursors (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

25. Data Definition Language (DDL) Statements (Transact-SQL)

They explain the data structures. Some of these 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)

26. 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 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)

27. Data Types (Transact-SQL)

It tells the kind of information any variable or expression carries. Some of the data type 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

28. Expressions (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.

29. Language Elements (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)

30. Management Commands

The commands for managing the SQL servers include the following:
• KILL QUERY NOTIFICATION SUBSCRIPTION
• RECONFIGURE
• CHECKPOINT
• KILL
• KILL STATS JOB
• SHUTDOWN
• DBCC

31. Operators (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

32. Predicates (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

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

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

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

36. 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)

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

38. System Views (Transact-SQL)

These functions showcase the metadata. And some of the functions are :
• Catalog Views
• Information Schema Views
• Compatibility Views
• Replication Views
• Dynamic Management Views and Functions

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

40. Variables (Transact-SQL)

For working on the variables, the functions in SQL are:
• Declare @local_variable
• SET @local_variable
• SELECT @local_variable

41. XML Statements (Transact-SQL)

SQL Server offers the below mentioned XML statements.
• WITH XMLNAMESPACES
• xml_schema_namespace

"0 Responses on SQL Functions"

Leave a Message

Your email address will not be published.

Training in Cities

Bangalore, Hyderabad, Chennai, Delhi, Kolkata, UK, London, Chicago, San Francisco, Dallas, Washington, New York, Orlando, Boston

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

top

Sales Offer

  • To avail this offer, enroll before 06th December 2016.
  • This offer cannot be combined with any other offer.
  • This offer is valid on selected courses only.
  • Please use coupon codes mentioned below to avail the offer
offer-june

Sign Up or Login to view the Free SQL Functions.