966
Problem
I need SQL Server Structured Query Language (T-SQL) examples to help me finish my job quickly. I want to be able to bookmark a resource and then use it on a daily basis when needed as I build apps. Can you please enumerate the most common commands as a SQL Server SQL Quick Reference guide?
Solution
The goal of this tip is to create a living document that helps Developers, DBAs, Data Analysts, etc., on a daily basis when working with the SQL Server DBMS on-premises or in the cloud (Azure, Amazon, AWS, or Google). Feel free to bookmark this article and suggest code to include in this tip in the comments section below.
If you are a beginner with the SQL Server relational database management system (RDBMS), consider the following articles as a starting point: Learning SQL Server and What is SQL Server.
Getting Started with SQL Basic Commands
| Command | Description | Resources |
|---|---|---|
| SELECT | Retrieve data from a table or view in a SQL database. SELECT is the most used DML (Data Manipulation Language) command in SQL programming. | |
| INSERT | Add records to an existing table or view as another DML command in the SQL language. | |
| UPDATE | Modify one or more columns and rows in a table or view, also a DML command. | |
| DELETE | Remove one or more records from a table or view as the final DML command. | |
| Stored Procedures | Compiled code stored in a SQL Server database that can be called as a single unit of logic. |
|
| Functions | Discreet set of logic to perform a single operation, such as converting a date, formatting a string, getting the system date, etc. | |
| Cheat Sheets | Listing of common T-SQL commands to help jumpstart your productivity. | |
| SSMS | SQL Server Management Studio is the most commonly used tool to query and manage SQL Server. |
SQL Server Tables and Views
| Command | Description | Resources |
|---|---|---|
| Table | Physical structure consisting of columns and rows to store data. Tables are specified in FROM and JOIN clauses to access them via SELECT, INSERT, UPDATE, and DELETE commands. |
|
| View | Virtual table that consists of a SELECT statement. The View can actually be a subset of columns from one table or multiple tables JOINed in the view to simplify data access. | |
| CREATE | Generally the first Data Definition Language (DDL) command used by SQL Server professionals to create objects such as tables, views, stored procedures, functions, indexes, etc. For example, the CREATE TABLE command creates a table with a specific schema owner, name, columns, column properties (data types, null, defaults), primary key, and foreign keys. | |
| ALTER | The second of the DDL commands that is used to change an object. For example, with a table, a column can be added, the length of a data type can be increased, the data type can be changed, etc. | |
| DROP | The third of the DDL commands and is used to remove an object from a database. Keep in mind that when an object is dropped without an explicit transaction, it is no longer available. With a table, that means the data is gone. With a stored procedure or function, the logic is gone. To recover an object, you would need to recover from a database backup or retrieve the latest version of code in your source control system. Often, it is safer to rename an object and let it reside in the database for a safe period of time before dropping it. | |
| Primary Key | The Primary Key is one or more columns that uniquely identifies a row. In general, all tables should have a Primary Key, which should be an Integer data type. Examples of a Primary Key could be OrderID, CustomerID, ProductID, etc. |
|
| Foreign Key | A Primary Key and a Foreign Key comprise a relationship between two tables called Referential Integrity. This ensures data properly relates to corresponding records once your database has been normalized. An example of a foreign key could be a CustomerID in the SalesOrderHeader table to know which customer will be billed for the transaction. |
|
| Data Types | In a table, data types are defined at the column level. Examples include integers (whole numbers), numeric or decimal, variable or fixed character strings, dates and times, money, etc. | |
| NULL | In a table, NULL is defined at the column level. NULL is an unknown value that can be stored as a valid value. NULL values can also be evaluated in SQL queries. | |
| Constraints | Constraints can be set up at a table level to ensure a specific rule is enforced to ensure data validity. Constraints include: Primary Key Foreign Key Unique Check Default | |
| Indexes | Indexes are created at a table level to bring order to the table, improve data access performance, and improve the user experience. There are numerous types of indexes for various use cases. | |
| Clustered | A clustered index physically orders the data according to the indexed columns. A table can only have one clustered index. The leaf nodes of the index store the data for the rest of the columns in the table, so when a lookup is performed on this type of index, no other structures need to be referenced. | |
| Non-Clustered | A non-clustered index (or regular b-tree index) is an index where the order of the rows does not match the physical order of the actual data. Instead, it is ordered by the columns that make up the index. In a non-clustered index, the leaf pages of the index do not contain any actual data but instead contain pointers to the actual data. These pointers would point to the clustered index data page where the actual data exists (or the heap page if no clustered index exists on the table). | |
| Filtered | A Filtered Index, which is an optimized non-clustered index, allows us to define a filter predicate, a WHERE clause, while creating the index. | |
| Columnstore | Columnstore indexes store the index data for each column on a specific page, so any type of scan query (i.e., data warehouse type queries) performs much better than if the column were indexed with a regular b-tree index. | |
| Trigger | A SQL Server Trigger is a block of procedural code executed when a specified event occurs with which the trigger is associated. The most common triggers are DML triggers that log events, such as when a user INSERTS, UPDATES, or DELETES a row in a table, which is issued in a SQL query or stored procedure. SQL triggers can also be used to log (insert into a log table) when a user connects to a database. SQL triggers can also record when a user or event modifies a table and more. |
Additional T-SQL Commands for SQL Databases
| Command | Description | Resources |
|---|---|---|
| TOP | Used in SELECT and DELETE commands to access a specified number or percentage of rows. | |
| COUNT | Used in SELECT code as an aggregate function that returns the number of rows in a specified table. | |
| DISTINCT | Used in a SQL SELECT statement to return a distinct set of values. | |
| COUNT DISTINCT | Combination of two aggregate functions. Used in SELECT code that returns the number of unique rows in a specified table. | |
| SELECT INTO | SELECT command that inserts data into a new table based on the logic specified. Often used for testing or backing up a subset of data. | |
| INSERT SELECT | INSERT command that adds records to an existing table based on logic specified in the SELECT statement. | |
| UNION and UNION ALL | Combine multiple datasets into one comprehensive dataset with numerous SELECT commands. | |
| INTERSECT and EXCEPT | Microsoft introduced the INTERSECT and EXCEPT operators to further extend what you could already do with the UNION and UNION ALL operators. INTERSECT – The final result set where values in both of the tables match EXCEPT – The final result set where data exists in the first dataset and not in the second dataset | |
| CASE | The CASE expression is used to build IF … THEN … ELSE statements into your T-SQL code. It is used within a SQL statement, such as SELECT or UPDATE. Don’t mistake CASE for the IF ELSE control-of-flow construct, which evaluates the conditional execution of SQL statements. |
|
FROM and JOIN Commands in MS SQL Server
| Command | Description | Resources |
|---|---|---|
| FROM Clause | Used in SELECT, UPDATE, and DELETE commands to specify the tables or views to access the needed data. | |
| JOIN | Used in the FROM clause of SELECT, UPDATE, or DELETE commands to match records between one or more tables or views. | |
| INNER JOIN | Used in SELECT, UPDATE, and DELETE commands to match records between one or more tables or views. | |
| LEFT JOIN | A LEFT OUTER JOIN between tables \ views A and B can be described as always taking all records from table A and returning any matching rows from table B in SELECT, UPDATE, and DELETE logic. | |
| RIGHT JOIN | A RIGHT OUTER JOIN between tables \ views A and B can be described as always taking all records from table B and returning any matching rows from table A in SELECT, UPDATE, and DELETE logic. | |
| CROSS APPLY | Microsoft introduced the APPLY operator in SQL Server 2005. It’s like a JOIN clause, which allows for the joining of two table expressions. The key difference between the JOIN and APPLY operators is when you have a table-valued expression on the right side and want to evaluate it for each row from the left table expression. | |
| OUTER APPLY | The APPLY operator is like a JOIN clause, which allows for the joining of two table expressions. The key difference between the JOIN and APPLY operators is when you have a table-valued expression on the right side and want to evaluate it for each row from the left table expression. |
T-SQL WHERE Clause Logic
| Command | Description | Resources |
|---|---|---|
| WHERE | Filter records to meet specific criteria in SELECT, UPDATE, or DELETE code with common logic listed below. | |
| AND, OR, NOT | Logical Operators used in the WHERE and HAVING clause with the following logic:
| |
| LIKE | The LIKE operator in SQL Server is used to compare a character string against a pattern. This pattern can include regular characters and wildcard characters. The LIKE operator is often used in the WHERE clause of a SQL statement to find rows that match a specified pattern:
| |
| IN | The IN operator is used to replace a group of arguments using the = operator that are combined with an OR in for SELECT, UPDATE, or DELETE statement. It can make code easier to read and understand. Generally, it will not change performance characteristics. | |
| NOT IN | The NOT IN operator replaces a group of arguments using the <> (or !=) operator combined with an AND. It can make code easier to read and understand for SELECT, UPDATE, or DELETE SQL commands. Generally, it will not change performance characteristics. | |
| NOT Equal | The SQL Server NOT Equal operators are used to test that one value, often a column, does not match the value of another. These operators can also be used in T-SQL code as a part of WHILE loops, IF statements, HAVING clauses, join predicates, SQL GROUP BY, or CASE statements. | |
| EXISTS | The EXISTS operator doesn’t check for values but instead checks for the existence of rows. Typically, a subquery is used in conjunction with EXISTS. It doesn’t matter what the subquery returns as long as rows are returned. | |
| NOT EXISTS | ||
| BETWEEN | The SQL BETWEEN operator is used in SELECT, UPDATE, DELETE, and INSERT commands in the WHERE clause, generally when trying to find values between dates or number ranges. NOT BETWEEN can also be used for logic outside of a specific range. | |
| NULL | A NULL value is a special marker in the column to denote that a value does not exist. It is important to understand that a NULL column value is different than having a blank (empty string) or 0 value in a column. E.g., ‘ ‘ or (empty string) <> NULL, 0 <> NULL. NOT NULL logic in the WHERE clause is used to find any non-NULL values. | |
| IS NOT NULL | The SQL IS NOT NULL condition is used to test for a non-NULL value. It returns TRUE if a non-NULL value is found and FALSE otherwise. | |
| ISNULL | The ISNULL() function is used to check if a value is null, and if it is, it will return the replacement value specified when calling the function. | |
| COALESCE | The SQL function COALESCE() is a basic SQL function used to evaluate a list of values and return the first non-NULL value. The COALESCE function is used as an alternative to the ISNULL() function or the CASE statement. The advantage over the ISNULL() function is that ISNULL() only takes two parameters, and COALESCE() can take many parameters. The advantage over a CASE statement is that it is easier to write and less code. |
Order By, Group By, and Having T-SQL Commands
| Command | Description | Resources |
|---|---|---|
| ORDER BY | Sort the result set for a SELECT statement in ascending (ASC) or descending (DESC) order. | |
| GROUP BY | Aggregate data from numerous rows with common data values. | |
| HAVING | The HAVING clause corresponds to the GROUP BY clause and can be considered a means to filter the final result set. Often, the HAVING clause has COUNT or SUM logic greater than a threshold. |
Intermediate and Advanced T-SQL Commands
| Command | Description | Resources |
|---|---|---|
| Subquery | Subqueries are queries that are nested inside another query or statement. They are permitted wherever SQL Server allows an expression and are indicated by enclosing the subquery in parenthesis. | |
| SQL IF, BEGIN, END, ELSE, ELSEIF | The IF statement is very simple to use. It works exactly like the WHERE clause of a SQL statement, except it isn’t attached to a query. The keyword IF is followed by an argument or group of arguments combined with AND or OR keywords. An argument is a logical comparison that evaluates to either true or false. Some examples of an argument might be “@NumberValue < 1”, “@TextValue =’Hello’ “, or “BooleanFunction()” | |
| Dynamic SQL | SQL Server offers a few ways of running a dynamically built SQL statement. Here are a few options: Writing a SELECT statement or SQL Query with SQL variables Using EXEC Using sp_executesql | |
| Cursor | A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose of the cursor may be to update one row at a time or perform an administrative process, such as SQL Server database backups, in a sequential manner. |
|
| WHILE | A WHILE loop is used to iterate over rows in a sequential manner. | |
| Temporary Tables | Temporary tables store data in a tabular format, i.e., columns and rows. Local temporary tables are only accessible from their creation context, such as the connection. Global temporary tables are accessible from other connections. Both local and global temp tables reside in the Tempdb database. |
|
| Table Variables | A table variable is a local variable created as a table type via a declaration statement that begins with an @ sign. It includes column names with data types and constraints. Table variables can be declared within batches, functions, and stored procedures used in SELECT, INSERT, UPDATE, and DELETE statements. Unlike permanent and temp tables, table variables cannot be created and populated via the INTO clause in a SELECT statement. | |
| MERGE | The MERGE operation merges data from a source result set to a target table based on a condition that you specify and whether the data from the source already exists in the target. The new SQL command combines the sequence of conditional INSERT, UPDATE, and DELETE commands in a single atomic statement, depending on the existence of a record. |
|
| CTE | A CTE is a ‘temporary named result set’ that remains in memory for the scope of a single execution of a SELECT, INSERT, UPDATE, DELETE, or MERGE statement. | |
| Random | Learn how to generate random data, dates, etc., or retrieve a random set of records from a table. |
|
| Ranking | ROW_NUMBER() returns a sequential integer for every row in our partition. ROW_NUMBER() starts again at 1 when SQL encounters a new partition. The RANK() function behaves like ROW_NUMBER() in that it returns a ranking based on the ORDER BY; however, RANK() distinguishes ties. DENSE_RANK() is almost identical to RANK(), except it doesn’t allow gaps. NTILE() divides the results into equal groups based on your input. | |
| Rollup | Rolling up data from multiple rows into a single row may be necessary for concatenating data, reporting, exchanging data between systems, and more. This can be accomplished by using PIVOT, STUFF, and FOR XML. | |
| Bitwise Operators | T-SQL provides bitwise operators to perform logical operations on SQL Server database table columns, including OR, AND, and XOR. | |
| SQL Injection | Many of us use dynamic SQL because we have requirements that dictate runtime choice—allowing the user to select the columns, table name, or even entire where clauses. There are different ways to implement dynamic SQL, and some are more prone to SQL injection than others. SQL injection is a major security threat, likely responsible for nearly any data breach you read about in the news. |
Next Steps
- We hope this is a useful SQL Quick Reference Guide for learning SQL syntax.
- Interested in another database back-ends and programming languages, then check out these tips:
- If there is a topic you would like included in this SQL Quick Reference Guide or want more information about a particular topic or command, please post your request in the comments section below.
