01 - Transact SQL - Functions

Hi,

 

-          Transact-SQL, an extension to the SQL database programming language, is a powerful language offering many features.

-          Transact-SQL provides the SQL Server developer with several useful functions, conditional processing methods, advanced transaction control, exception and error handling, scrollable cursors, and much more.

 

Here we'll cover below –

 

-          Aggregate Functions

-          String Functions

-          Date Functions

-          @@ Functions

-          Mathematical Functions

-          Security functions

-          Meta Data Functions

-          System Functions

-          IMAGE, TEXT, and NTEXT Functions - Pending

-          Information Schema Views

 

 

Aggregate Functions

 

-          Can be used within SELECT, COMPUTE, COMPUTE BY, and HAVING clauses, but cannot be used in a WHERE clause.

-          Can be used to calculate the summary values of the non-null values in a particular column, and can be applied to all the rows in a table, producing a single value (a scalar aggregate).

 

 

 

 

Aggregate_function ([ALL | DISTINCT] expression)
AVG COUNT MAX MIN SUM	-    COUNT is the only aggregate function that does not ignore NULL values. -    COUNT DISTINCT <expression> does ignore NULLs, counting the number of non-null values of the <expression>
BINARY_CHECKSUM CHECKSUM CHECKSUM_AGG
COUNT_BIG GROUPING
STDEV STDEVP VAR VARP

 

 

String Functions

 

-          String functions perform operations against a character data type, and return a value depending on the function type used.

-          String functions are scalar functions, meaning they operate on one value, and return a single value.

-          String functions can be used anywhere an expression is permitted, including within the SELECT and WHERE clauses.

-          String functions can also be nested within other string functions.

 

Syntax –

 

function_name (arguments)

 

 

           

ASCII CHAR CHARINDEX PATINDEX DIFFERENCE and SOUNDEX
LEFT and RIGHT LEN LOWER and UPPER LTRIM and RTRIM
NCHAR and UNICODE REPLACE and STUFF QUOTENAME REPLICATE REVERSE
SPACE STR SUBSTRING CONVERT and CAST

 

 

Date Functions

 

-          Date functions are used to convert, manipulate, present, and calculate differences of DATETIME or SMALLDATETIME data types.

-          DATETIME data type date ranges are from January 1, 1753 through December 31, 9999.

-          SMALLDATETIME ranges from January 1, 1900, through June 6, 2079.

 

 

 

DATEADD DATEDIFF DATENAME DATEPART	DATEADD  (<part_of_date_for_new_value>, <integer_increment_or_decrement>,<date_to_modify>) DATEDIFF (<part_of_date_for_new_value>, <first_date>, <second_date>) DATENAME (<part_of_date_for_new_value>, <date>) DATEPART (<part_of_date_for_new_value>, <date>)     The possible values of the first parameter.   -    yy or yyyy for Year -    qq or q for Quarter -    mm or m for Month -    dy or y for Dayofyear (day of year) -    dd or d for Day -    wk or ww for Week -    hh for Hour -    mi or n for Minute -    ss or s for Second -    ms for Millisecond
DAY GETDATE GETUTCDATE
MONTH YEAR

 

 

@@ Functions

 

-          The @@ functions are used to return information.

-          @@ functions (called global variables in previous versions of SQL Server) do not take or expect parameters.

 

 

@@CPU_BUSY @@DATEFIRST @@DBTS @@IDLE @@BUSY @@LANGID @@LOCK_TIMEOUT @@MAX_CONNECTIONS @@MAX_PRECISION @@NESTLEVEL @@OPTIONS
@@PACK_RECEIVED @@PACK_SENT @@PACKET_ERRORS @@REMSERVER @@SERVICENAME @@SPID @@TEXTSIZE @@TIMETICKS @@TOTAL_READ @@TOTAL_WRITE
@@CONNECTIONS @@ERROR @@IDENTITY @@LANGUAGE @@PROCID @@ROWCOUNT @@SERVERNAME @@TRANSCOUNT @@TOTAL_ERRORS @@VERSION

 

 

Mathematical Functions

 

-          Mathematical functions return values often needed for operations on numeric data.

-          The general form for mathematical functions is:

 

function_name(arguments)

 

 

ABS CEILING EXP FLOOR LOG PI RAND RODUND SQUARE SQRT
ACOS ASIN ATAN COS COT DEGREES POWER RADIANS

 

 

Security functions

 

-          Security functions are used to return information about User And Role Security.

-          They can be used independently of any other expression, or used within a Transact-SQL expression.

 

 

HAS_DBACCESS IS_MEMBER IS_SRVROLEMEMBER SUSER_SID SUSER_SNAME USER_ID USER_NAME

 

Meta Data Functions

 

-          Meta data functions are used to return data about the database and the objects within.

-          They can be used independently of any other expression, or used within a Transact-SQL expression.

 

COL_LENGTH DB_ID DB_NAME OBJECT_ID OBJECT_NAME
FILE_ID FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEPROPERTY

 

System Functions

 

-          System functions are used to return information on an array of SQL Server settings and user connection context properties.

-          Some system functions are also used to process arguments independently of system or connection settings (like COALESCE or ISNULL, for example).

-          They can be used independently of any other expression, or used within a Transact-SQL expression

 

 

APP_NAME COALESCE CURRENT_USER DATALENGTH GETANSINULL HOST_ID IDENT_CURRENT IDENT_INCR IDENT_SEED ROWCOUNT_BIG SESSION_USER STATS_DATE
CURRENT_TIMESTAMP HOST_NAME ISDATE ISNUMERIC ISNULL NULLIF SCOPE_IDENTITY SYSTEM_USER USER	-                 ISNULL validates whether an expression is NULL and, if so, replaces the NULL value with an alternative value.   ISNULL (<expression_to_validate>, <replacement_value>)   -                 NULLIF returns a null value when the two provided expressions are the same value; otherwise, the first expression is returned.   NULLIF (<expression_1>, <expression_2>)

 

 

Information Schema Views

 

-          Information schema views provide system views for accessing database object meta data, such as tables, columns, privileges, and constraints.

-          Whenever possible, you should use information schema views instead of accessing SQL Server database system tables directly.

-          Accessing system tables directly can cause problems when Microsoft decides to change the system table structure, or naming, or columns, along with the damage that can be done if incorrect updates are made.

-          In each case, the view must be prefaced by INFORMATION_SCHEMA.view_name.

-          The view will only return data from database objects to which the current user has access.

-          For example, the CONSTRAINT_COLUMN_USAGE view will only display columns with constraints for those tables that the user has permissions to view.

 

 

CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINT
ROUTINE_COLUMNS
ROUTINES
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS

 

 

            e.g

 

SELECT TABLE_NAME, COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE = 'ntext'

 

 

 

 

Thanks & Regards,

Arun Manglick || Tech Lead