Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

Transact-SQL Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Overview

Transa­ct-SQL is the language used to query data in Microsoft SQL Server and Azure SQL Database.
SQL is declar­ative, not proced­ural.
Data is stored in tables, which may be related to one another through common key fields (primary and foreign).
Schemas are namespaces for database objects.
Fully-­qua­lified name for an object
 
[serve­r_n­ame­].[­dat­aba­se_­nam­e].[­sc­hem­a_n­ame­].o­bje­ct_name
Best practice within database context
 
 schema­_na­me.o­bj­ect­_name
Always terminate a SQL statement with a semi-c­olon, 
;
.
Relational Databases

SQL Statement Types

DML
Data Manipu­lation Language
Statements for querying and modifying data.
 
SELECT, INSERT, UPDATE, DELETE
DDL
Data Definition Language
Statements for defining database objects.
 
CREATE, ALTER, DROP
DCL
Data Control Language
Statements for assigning permis­sions.
 
GRANT, REVOKE, DENY

NULL

NULL
NULL is used to indicate an unknown or missing value.
NULL is not equivalent to zero or an empty string. Arithmetic or string concat­enation operations involving one or more NULL operands return NULL. For example, 12 + NULL = NULL.
 
ISNULL­(co­lum­n/v­ari­able, value)
Return value if the column or variable is 
NULL
.
If you need to compare a value to NULL, use the IS operator instead of the = operator.
 
NULLIF­(co­lum­n/v­ari­able, value)
Returns 
NULL
if the column or variable is value.
The NULLIF function returns NULL when a column or variable contains a specified value.
 
COALES­CE(­col­umn­/va­ria­ble1, column­/va­ria­ble2, ...)
Returns the value of the first non-
NULL
column or variable in the list.
The COALESCE function returns the first non-NULL value in a specified list of columns or variables.

SELECT Statement

Main query clauses in keyed-in order
 
SELECT
<select list>
Defines which columns to return.
 
FROM
<table source>
Defines table(s) to query.
 
WHERE
<search condit­ion>
Filters rows using a predicate.
 
GROUP BY
<group by list>
Arranges rows by groups.
 
HAVING
<search condit­ion>
Filters groups using a predicate.
 
ORDER BY
<order by list>
Sorts the output.
Logical query processing order
 
FROM
 
WHERE
 
GROUP BY
 
HAVING
 
SELECT
 
ORDER BY
Notes
Use the 
SELECT
statement to retrieve a rowset of data from tables and views in a database.
In the 
SELECT
clause, you can use 
*
to return all columns, but generally you should specify explicit columns.
You can specify expres­sions in the 
SELECT
clause to return the results of calcul­ations.
You can use the 
AS
keyword to specify aliases for columns in the rowset returned by the 
SELECT
statement.
By default, the SELECT statement returns all rows. If mulitple rows contain the same values for every column, they are duplicated in the results. Using the DISTINCT keyword eliminates duplic­ates, ensuring that only one row for each distinct combin­ation of column values is returned.
The order of rows in the result of a SELECT statement is not guaranteed unless you explicitly specify one or more columns in an ORDER BY clause. You can specify sort direction as ASC (the default) or DESC.
You can combine the ORDER BY clause with the TOP keyword to retrict the results so that they include only the top n rows (where n is the number or percentage of rows you want to return).
You can implement a query to retrieve a specified "­pag­e" of results by using the OFFSET and FETCH keywords with the ORDER BY clause.
Use the WHERE clause to filter the results returned by a SELECT query based on a search condition.
A search condition is composed of one or more predic­ates.
Predicates include condit­ional operators (such as =, >, and <), IN, LIKE, and NOT.
You can use AND and OR to combine predicates based on Boolean logic.
Example
SELECT country, YEAR(h­ire­date) AS yearhired, COUNT(*) AS numemp­loyees
FROM HR.Emp­loyees
WHERE hiredate >= '20140101'
GROUP BY country, YEAR(h­ire­date)
HAVING COUNT(*) > 1
ORDER BY country, yearhired DESC;

Data Types

Transa­ct-SQL supports a wide range of data types, which can be broadly catego­rized as exact numeric, approx­imate numeric, character, date/time, binary, and other (which includes specia­lized data types for handling data such as XML and spatial data).
Some data types are compat­ible, and values can be implicitly converted between them. Conversion between other data types requires the use of explicit conversion functions.