T-SQL (Transact-SQL) is an extension of SQL language. This tutorial covers the fundamental concepts of T-SQL such as its various functions, procedures, indexes, and transactions related to the topic.
| Convention | Used for |
|---|---|
| UPPERCASE | Transact-SQL keywords. |
| italic | User-supplied parameters of Transact-SQL syntax. |
| bold | Type database names, table names, column names, index names, stored procedures, utilities, data type names, and text exactly as shown. |
| underline | Indicates the default value applied when the clause that contains the underlined value is omitted from the statement. |
| | (vertical bar) | Separates syntax items enclosed in brackets or braces. You can use only one of the items. |
[ ] (brackets)
|
Optional syntax items. Don't type the brackets. |
| { } (braces) | Required syntax items. Don't type the braces. |
| [ ,... n] | Indicates the preceding item can be repeated n number of times. The occurrences are separated by commas. |
| [... n] | Indicates the preceding item can be repeated n number of times. The occurrences are separated by blanks. |
| ; | Transact-SQL statement terminator. Although the semicolon isn't required for most statements in this version of SQL Server, it will be required in a future version. |
| <label> ::= | The name for a block of syntax. Use this convention to group and label sections of lengthy syntax or a unit of syntax that you can use in more than one location within a statement. Each location in which the block of syntax could be used is indicated with the label enclosed in chevrons: <label>.
A set is a collection of expressions, for example <grouping set>; and a list is a collection of sets, for example <composite element list>. |
Unless specified otherwise, all Transact-SQL references to the name of a database object can be a four-part name in the following form:
*server\_name*.\[ *database\_name*\].\[ *schema\_name*\]. *object\_name* | *database\_name*.\[ *schema\_name*\]. *object\_name* | *schema\_name*. *object\_name* | *object\_name*
server_name Specifies a linked server name or remote server name.
database_name Specifies the name of a SQL Server database when the object resides in a local instance of SQL Server. When the object is in a linked server, database_name specifies an OLE DB catalog.
schema_name Specifies the name of the schema that contains the object if the object is in a SQL Server database. When the object is in a linked server, schema_name specifies an OLE DB schema name.
object_name Refers to the name of the object.
When referencing a specific object, you don't always have to specify the server, database, and schema for the SQL Server Database Engine to identify the object. However, if the object can't be found, an error is returned.
| DataType | Description |
|---|---|
| binary | Fixed-length binary data with a maximum length of 8,000 bytes. |
| varbinary | Variable-length binary data with a maximum length of 8,000 bytes. |
| varbinary(max) | Variable-length binary data with a maximum length of 2 31 bytes (Introduced in SQL Server 2005). |
| image | Variable-length binary data with a maximum length of 2,147,483,647 bytes. |
| DataType | Description |
|---|---|
| char | Fixed-length non-Unicode character data with a maximum length of 8,000 characters. |
| varchar | Variable-length non-Unicode data with a maximum of 8,000 characters. |
| varchar(max) | Variable-length non-Unicode data with a maximum length of 231 characters (Introduced in SQL Server 2005). |
| text | Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters |
| DataType | Description |
|---|---|
| nchar | Fixed-length Unicode data with a maximum length of 4,000 characters. |
| nvarchar | Variable-length Unicode data with a maximum length of 4,000 characters. |
| Nvarchar(max) | Variable-length Unicode data with a maximum length of 2 30 characters (Introduced in SQL Server 2005). |
| ntext | Variable-length Unicode data with a maximum length of 1,073,741,823 characters. |
| DataType | Description |
|---|---|
| datetime | Jan 1, 1753 to Dec 31, 9999 (3.33 milliseconds accuracy) |
| smalldatetime | Jan 1, 1900 to Jun 6, 2079 (1 minute accuracy) |
| datetimeoffset | Jan 1, 0001 to Dec 31, 9999 (100 nanoseconds accuracy. Introduced in SQL Server 2008) |
| datetime2 | Jan 1, 0001 to Dec 31, 9999 (100 nanoseconds accuracy. Introduced in SQL Server 2008 ) |
| time | 00:00:00.0000000 to 23:59:59.9999999 (100 nanoseconds accuracy. Introduced in SQL Server 2008 ) |
| DataTypes | Description |
|---|---|
| bigint | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| int | -2,147,483,648 to 2,147,483,647 |
| smallint | -32,768 to 32,767 |
| tinyint | 0 to 255 |
| bit | 0 or 1 |
| decimal | -10 38 +1 to 1038 –1 |
| numeric | -10 38 +1 to 1038 –1 |
| money | -922,337,203,685,477.5808 to +922,337,203,685,477.5807 |
| smallmoney | +214,748.3647 |
- sql_variant − Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
- timestamp − Stores a database-wide unique number that gets updated every time a row gets updated.
- uniqueidentifier − Stores a globally unique identifier (GUID).
- xml − Stores XML data. You can store XML instances in a column or a variable (Introduced in SQL Server 2005).
- cursor − A reference to a cursor.
- table − Stores a result set for later processing.
- hierarchyid − A variable length, system data type used to represent position in a hierarchy (Introduced in SQL Server 2008).
SQL Server SELECT statement is used to fetch the data from a database table which returns data in the form of result table. These result tables are called result-sets . Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server
Following is the basic syntax of SELECT statement. Where, column1, column2...are the fields of a table whose values you want to fetch.
SELECT column1, column2, columnN FROM table_name;
<SELECT statement> ::= [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ] <query_expression> [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] } [ ,...n ] ] [ <FOR Clause>] [ OPTION ( <query_hint> [ ,...n ] ) ] <query_expression> ::= { <query_specification> | ( <query_expression> ) } [ { UNION [ ALL ] | EXCEPT | INTERSECT } <query_specification> | ( <query_expression> ) [...n ] ] <query_specification> ::= SELECT [ ALL | DISTINCT ] [TOP ( expression ) [PERCENT] [ WITH TIES ] ] < select_list > [ INTO new_table ] [ FROM { <table_source> } [ ,...n ] ] [ WHERE <search_condition> ] [ <GROUP BY> ] [ HAVING < search_condition > ]
The MS SQL Server WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables. If the given condition is satisfied, only then it returns a specific value from the table. You will have to use WHERE clause to filter the records and fetch only necessary records.
Specifies the search condition for the rows returned by the query.
Following is the basic syntax of SELECT statement with WHERE clause
SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2 ORDER BY column1, column2
-- Syntax for SQL Server and Azure SQL Database -- ISO-Compliant Syntax
GROUP BY { column-expression | ROLLUP ( <group_by_expression> [ ,...n ] ) | CUBE ( <group_by_expression> [ ,...n ] ) | GROUPING SETS ( <grouping_set> [ ,...n ] ) | () --calculates the grand total } [ ,...n ] <group_by_expression> ::= column-expression | ( column-expression [ ,...n ] ) <grouping_set> ::= () --calculates the grand total | <grouping_set_item> | ( <grouping_set_item> [ ,...n ] ) <grouping_set_item> ::= <group_by_expression> | ROLLUP ( <group_by_expression> [ ,...n ] ) | CUBE ( <group_by_expression> [ ,...n ] ) -- For backward compatibility only. -- Non-ISO-Compliant Syntax for SQL Server and Azure SQL Database GROUP BY [ ALL ] column-expression [ ,...n ]
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.
The MS SQL Server ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sort query results in ascending order by default.
Following is the basic syntax of ORDER BY clause.
-- Syntax for SQL Server and Azure SQL Database ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ] [ <offset_fetch> ] <offset_fetch> ::= { OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } [ FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY ] }
Specifies a search condition for a graph. MATCH can be used only with graph node and edge tables, in the SELECT statement as part of WHERE clause.
MATCH (<graph_search_pattern>) <graph_search_pattern>::= { <simple_match_pattern> | <arbitrary_length_match_pattern> | <arbitrary_length_match_last_node_predicate> } <simple_match_pattern>::= { LAST_NODE(<node_alias>) | <node_alias> { { <-( <edge_alias> )- } | { -( <edge_alias> )-> } <node_alias> | LAST(<node_alias>) } } [ { AND } { ( <simple_match_pattern> ) } ] [ ,...n ] <node_alias> ::= node_table_name | node_table_alias <edge_alias> ::= edge_table_name | edge_table_alias <arbitrary_length_match_pattern> ::= { SHORTEST_PATH( <arbitrary_length_pattern> [ { AND } { <arbitrary_length_pattern> } ] [ ,…n] ) } <arbitrary_length_match_last_node_predicate> ::= { LAST_NODE( <node_alias> ) = LAST_NODE( <node_alias> ) } <arbitrary_length_pattern> ::= { LAST_NODE( <node_alias> ) | <node_alias> ( <edge_first_al_pattern> [<edge_first_al_pattern>…,n] ) <al_pattern_quantifier> } | ( {<node_first_al_pattern> [<node_first_al_pattern> …,n] ) <al_pattern_quantifier> LAST_NODE( <node_alias> ) | <node_alias> } <edge_first_al_pattern> ::= { ( { -( <edge_alias> )-> } | { <-( <edge_alias> )- } <node_alias> ) } <node_first_al_pattern> ::= { ( <node_alias> { <-( <edge_alias> )- } | { -( <edge_alias> )-> } ) } <al_pattern_quantifier> ::= { + | { 1 , n } } n - positive integer only.
A Transact-SQL local variable is an database object that can store a single data value of a specific type. To declare a variable uses the keyword DECLARE, assign a variable name and a data type.
DECLARE @MyVariable datatype;
After a variable is declared, it gets the default NULL value. To assign a value to a variable, use the SET statement.
DROP PROCEDURE IF EXISTS [<db user>].[<sp name>]
DROP PROCEDURE IF EXISTS [<db user>].[<sp name>]
| + | Add |
| - | Subtract |
| * | Multiply |
| / | Divide |
| % | Modulo |
| & | Bitwise AND | |
| Bitwise OR | ||
| ^ | Bitwise exclusive OR |
| = | Equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| <> | Not equal to |
| += | Add Equals | |
| -= | Subtract Equals | |
| *= | Multiply Equals | |
| /= | Divide Equals | |
| %= | Modulo Equals | |
| &= | Bitwise AND Equals | |
| ^-= | Bitwise Exclusive Equals | |
| *= | Bitwise OR Equals |
| ALL | TRUE if all of a set of comparisons are TRUE. |
| AND | TRUE if both expressions are TRUE. |
| ANY | TRUE if any one of a set of comparisons are TRUE. |
| BETWEEN | TRUE if the operand is within the range of comparisons. |
| EXISTS | TRUE if a subquery contains any rows. |
| IN | TRUE if the operand is equal to one of a list of expressions. |
| LIKE | TRUE if the operand matches a pattern. |
| NOT | Reverses the value of any other operator. |
| OR | TRUE if either expression is TRUE. |
| SOME | TRUE if some of a set of comparisons are TRUE. |
References
- docs.microsoft.com - transact-sql-syntax-conventions-transact-sql
- docs.microsoft - Joins
- docs.microsoft - Tutorial writing transact sql statements
- TSQL info
- Tutorials Point - TSQ
- docs.microsoft - Tutorial writing transact sql statements
- Search SQL Server - TSQL
- SQL Server Central - understanding outer joins in sql
- Sisense - sql symbol cheatsheet