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
-
- 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 RODUND SQUARE SQRT | |
| ACOS ASIN ATAN 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 | |
| | |
| | |
-
- 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
No comments:
Post a Comment