MSSQL Cheat Sheet

- Database -
MS SQL Cheat Sheet

What`s inside

MS SQL guide / cheat sheet for everyday use.

(from beginners to advanced users).

Usage

The guide is divided into sections with similar objectives.

The details are kept brief  and focuses directly on practical examples using Transact-SQL (followup links are provided wherever necessary).

Database
MS SQL Cheat Sheet
Jump To

Introduction

  • ⇒ Owned by Microsoft
  • ⇒ Initial code was developed by Sybase Inc. in 1980’s
  • ⇒ A Relational Database Management System (RDMS)
  • ⇒ A Client-Server Database
  • ⇒ Supports ANSI SQL (the standard SQL Language)
  • ⇒ Contains T-SQL (SQL Servers own SQL Implementation)
  • ⇒ Interface Tool : SQL Server Management Studio (SSMS)

.

Download Links

SQLOS

An application layer where all operating system resources are managed. Tasks include :

  • ⇒ Memory and buffer management
  • ⇒ Non preemptive scheduling
  • ⇒ Resource governance
  • ⇒ Exception handling
  • ⇒ Deadlock detection
  • ⇒ Extended events and IO etc.
. Relational Database Engine

Receives query requests, parses them, determine the best way to execute the query and finally requests the storage engine for data based on the optimized query.

. Storage Engine

Stores and retrieves data from storage systems such as disks and SAN (Storage area networks).

.
  • ⇒ Always Encrypted
  • ⇒ Row level security
  • ⇒ Dynamic Data Masking (DDM)
  • ⇒ Transparent data encryption
  • ⇒ Fine-grained auditing
  • ⇒ Transport Layer Security

.

Tables

Contains a collection of rows, also referred to as records or tuples, and columns, also referred to as attributes.

.

Schemas

One or more object ownership groups contained within a database.

Each schema holds database objects such as tables, views and stored procedures.

.

.

master db

Records all the system-level information for an instance of SQL Server. (logon accounts, endpoints, linked servers, system configuration settings etc)

. msdb db

Used by SQL Server Agent for scheduling alerts and jobs.

. model db

Used as the template for all databases created on the instance of SQL Server.

. Resource db

A read-only database that contains system objects that are included with SQL Server.

. tempdb db

A workspace for holding temporary objects or intermediate result sets.

. .
Data Definition Language (DDL) :

Used to define the database schema.

example: Create, Drop, Alter, Rename etc.

. Data Manipulation Language (DML) :

Used to manipulate data present in a database.

example: Select, Insert, Update, Delete

. Data Control Language (DCL) :

Deals with the rights, permissions and other controls of a database system.

example: Grant, Alter, Revoke

. Transaction Control Language (TCL) :

Deals with transactions within a database.

example: Commit, Rollback, Savepoint, Set Transaction

.

Basics

MS SQL Query Flow
Select * FROM [ServerName].[DatabaseName].[Schema].[TableName]
.
Select Data from multiple data sources
.
Logical Processing Order
.
Sources
.
Samples
.
[ For more detail ]
Insert new records in a table.
.
Update records in a table.
.
A delete command functions as a transaction.

TSQL

Style 1

Select * From
Products; — This is a comment

.

Style 2

/*
This is a C style, multiline comment
Everything in the middle gets commented.
*/

.

Local Variables . Cursors . Table Variable
Blocks code execution for a specified time.

.

Syntax

WAITFOR
{
DELAY ‘time_to_pass’
| TIME ‘time_to_execute’
| [ ( receive_statement )
| ( get_conversation_group_statement ) ][ , TIMEOUT timeout ]}

.

Delay

WaitFor DELAY ’00:00:02′;
Print ‘This is print after a 2 sec delay.’;

.

Time

WAITFOR TIME ’22:20′;
PRINT ‘You will get the output at 10:20 P.M’;

.

[ For more detail ]

Syntax

IF Boolean_expression
{ sql_statement }
[ ELSE
{ sql_statement } ]

.

Examples

.

Syntax

WHILE Boolean_expression { sql_statement | BREAK | CONTINUE }

. Examples .
Syntax

LabelName: { code_to_execute } GOTO LabelName;

. Examples .
  • ⇒ Local temporary table : #TableName
  • ⇒ Global temporary table : ##TableName
. .

Using Table Variable

.

Create/Alter Syntax

.

Delete

DROP PROCEDURE stored_procedure_name;

.

Local Execution

EXECUTE|EXEC sp_recompile spName ‘param1’, ‘param2’

.

Remote Execution

EXEC Server.Database.Schema.spName

.

Examples

.

[ For more detail ]

A temporary result set that can be referenced within another SELECT, INSERT, UPDATE, or DELETE statement.
. Samples . [ For more detail ]

Transaction

  • 1. Identity Insert Check
  • 2. Nullability Constraint
  • 3. Data-type check
  • 4. INSTEAD OF trigger execution
  • 5. Primary-key constraint
  • 6. Check constraints
  • 7. Foreign-key constraint
  • 8. DML execution and update to the transaction log
  • 9. AFTER trigger execution
  • 10. Commit transaction

.

Atomic

All or nothing. Each transaction is a complete unit of work, the entire transaction either succeeds or fails. No partial transactions.

. Consistent

Each transaction preserves database consistency. The database goes from one consistent state to another consistent state.

. Isolated

Each transaction is separated, or concealed, from the actions of other transactions.

. Durable

Each transaction is permanent, regardless of system failure.

.

sys Utils

Query 1

SELECT * FROM sys.sql_modules WHERE object_id = OBJECT_ID(‘the-object-name’);

. Query 2

SELECT OBJECT_DEFINITION (OBJECT_ID(‘the-object-name’)) AS ObjectDefinition;

. Query 3

EXEC sp_helptext ‘the-object-name’

.

SELECT [Name], Product, Provider, Data_Source FROM sys.servers WHERE Is_Linked = 1;

.

Retrieves information about any object, in sysobjects or any user-defined data type in the systypes table.

.

List information about each object in a database.

EXEC sp_help;
GO

.

Display information about a single object.

EXEC sp_help ‘Person.Person’;

.

.
.
.

Full Text Search

⇒ Lets users and apps run full-text queries against character-based data in SQL Server tables.

⇒ Queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on the rules of a particular language such as English or Japanese.

. Set up full-text search
. Manage Catalogs

Object Explorer → DBName → Storage → Full Text Catalogs

. Set up full-text search .
CONTAINS and CONTAINSTABLE

To match words and phrases

. FREETEXT and FREETEXTTABLE

To match the meaning, but not the exact wording

. Query Samples .

Views

Saved queries that can be used as data sources.

. Types
  • ⇒ Indexed views
  • ⇒ Partitioned views
  • ⇒ System views
. Use Cases
  • ⇒ De-normalize or flatten complex joins
  • ⇒ Simplifying aggregate queries
  • ⇒ Creating useful assembly blocks
. .

Using SQL Server Management Studio

. Using Transact-SQL .

An optional clause that ensures the consistency of the view by enforcing the where clause while inserting and updating through the view.

. .

Helps prevent Schema changes which might result in a View breakage.

. .

User Defined Functions

A Transact-SQL or common language runtime routine that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value.

.

Types

  • ⇒ Scalar Functions
  • ⇒ Table-Valued Functions
  • ⇒ System Functions

.

.

For more detail

Link 1 | Link 2

For more examples

.

For more examples

.

For more examples

.

Paging

  • ⇒ Offset = 0 for the First fetch.
  • ⇒ You can then supply the last RowNum as the offset starting from the next fetch.
  • ⇒ You have to handle the next, previous according to the RowNum
  • ⇒ You can also make use of the Total count in order to support paging like googles 1,2,3,4,5
.

Basic Powershell

⇒ Open Powershell in Admin mode

⇒ Type:

> Install-Module SQLSERVER > Install-Module SQLSERVER -AllowClobber

⇒ DBA Tools

. Usage with SQL Server

⇒ Performing Repeatable tasks

⇒ Updating changes to multiple machines

⇒ Configuring cloud services

. [ For more detail]
Methods

⇒ SQLPS

⇒ Invoke-SQLcmd (run native T-SQL commands)

⇒ Declare SQL connection object

. Example
Methods

⇒ SQLPS (New-Object cmdlet)

⇒ Native T-SQL (Create Database command)

. Using SQLPS . Using Native T-SQL
Methods

⇒ SQLPS /SMO (New-Object cmdlet)

⇒ Native T-SQL (Create Database command)

. Using SQLPS . Using Native T-SQL

Data Manipulation

[Grab the snippet Here]

Syntax
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) Convert(varchar,GetDate(), 0)
. Usage
Select Convert(varchar,GetDate(), 0); — Output : Jul 23 2019 12:56PM — Style : 0
. Results with different styles
Style | Output 0 | mon dd yyyy hh:miAM (or PM) 1 | mm/dd/yy 2 | yy/mm/dd 3 | dd/mm/yy 4 | dd.mm.yy 5 | dd-mm-yy 6 | dd mon yy 7 | mon dd, yy 8 | hh:mi:ss 9 | mon dd yyyy hh:mi:ss:mmmAM (or PM) 10 | mm-dd-yy 11 | yy/mm/dd 12 | yymmdd 13 | dd mon yyyy hh:mi:ss:mmm(24h) (European Std.) 14 | hh:mi:ss:mmm(24h) 20 | yyyy-mm-dd hh:mi:ss(24h) (ODBC canonical) 21 | yyyy-mm-dd hh:mi:ss.mmm(24h) 22 | mm/dd/yy hh:mi:ss A/PM 23 | yyyy-mm-dd 24 | hh:mm:ss 25 | yyyy-mm-dd hh:mi:ss.mmm(24h) 100 | mm dd yyyy hh:mmA/PM 101 | mm/dd/yyyy (US Std.) 102 | yyyy.mm.dd (ANSI Std.) 103 | dd/mm/yyyy (British/French Std.) 104 | dd.mm.yyyy (German Std.) 105 | dd-mm-yyyy 106 | dd mon yyyy 107 | mon dd, yyyy 108 | hh:mi:ss 109 | mon dd yyyy hh:mi:ss:mmmAM (or PM) 110 | mm-dd-yyyy (US standard) 111 | yyyy/mm/dd (Japan Std.) 112 | yyyymmdd (ISO Std.) 113 | dd mon yyyy hh:mi:ss:mmm(24h) (European Std.) 114 | hh:mi:ss:mmm(24h) 120 | yyyy-mm-dd hh:mi:ss(24h) (ODBC canonical) 121 | yyyy-mm-dd hh:mi:ss.mmm(24h) 126 | yyyy-mm-ddThh:mi:ss.mmm (no spaces) 130 | dd mon yyyy hh:mi:ss:mmmAM (Hijri) 131 | dd/mm/yyyy hh:mi:ss:mmmAM (Hijra)
Syntax
FORMAT ( value, format [, culture ] ) FORMAT (GETDATE(), ‘dd-MM-yy’) FORMAT (GETDATE(), ‘d’, ‘en-US’ )
. Usage
SELECT FORMAT (GETDATE(), ‘dd-MM-yy’) AS DATE; — Output : 01-09-19
. Results with different styles . [ For more detail ]
Examples

Built-in scalar string functions that perform an operation on a string and return a string or numeric value.

. Examples

Built-in functions that perform an operation on numeric values.

. Examples

Joins & Unions

Definition

A Join is a means for combining columns from one (self-join) or more tables by using values common to each. They merge data horizontally.

.

Types

The ANSI-standard SQL specifies five types of JOIN:

  • ⇒ Inner Join
  • ⇒ Left Outer Join
  • ⇒ Right Outer Join
  • ⇒ Full Outer Join
  • ⇒ Cross Join

.

  • ⇒ Also called the Natural Join
  • ⇒ An intersection (common information) between two datasets.

.

ANSI-Standard Syntax

SELECT *
From DataSet1 as D1 Inner Join DataSet2 as D2
On D1.columnID = D2.columnID

.

non-ANSI Syntax / Old style

SELECT *
From DataSet1 as D1, DataSet2 as D2
WHERE D1.columnID = D2.columnID

.

It is recommended to use the more readable ANSI-Standard JOIN clauses instead of the old style joins.

.
Example

.

Left Outer Join

All the rows from the table in the left of the join and only matching rows from the right side of the join.

.

Right Outer Join

All the rows from the table in the right of the join and only matching rows from the left side of the join.

.

  • ⇒ Left Join and Left Outer Join are equivalent.
  • ⇒ Right Join and Right Outer Join are equivalent.

.

Syntax

SELECT *
From DataSet1 as D1 Left | Right [Outer] Join DataSet2 as D2
On D1.columnID = D2.columnID

.

Example

.

All data is returned from both tables regardless of matching data.

.

Syntax

SELECT *
From DataSet1 as D1 Full [Outer] Join DataSet2 as D2
On D1.columnID = D2.columnID

.

Example

.

  • ⇒ A Cartesian product (multiplication) between two data sources.
  • ⇒ All rows in the 1st table matches up with all rows in the 2nd table
. Syntax

SELECT col1, col2 From DataSet1 Cross Join DataSet2

. Example .
  • ⇒ Joining from one table back to the same table
  • ⇒ Typically used for hierarchical structures
. Syntax

SELECT * From DataSet1 as D1 Inner Join DataSet1 as D2 On D1.columnID = D2.columnID

. Example .
  • ⇒ Combines the results of two or more queries into a distinct single result
  • ⇒ Each SELECT statement within UNION must have the same number of columns, similar data types and must be in the same order

.

Syntax

SELECT col1, col2 FROM DataSet1
Union | Union All | Intersect | Except
SELECT col1, col2 FROM DataSet2;

.

  • ⇒ Union: select only distinct data
  • ⇒ Union All: allow duplicates
  • ⇒ Intersect: Only those that are common to both (Vertical stacking unlike a join`s horizonal merge)
  • ⇒ Except: Only those that are either in the first or the second select (uncommon to both)

.
Example

.

Used to join a table with the output of a table valued function.

. Types
  • ⇒ Cross Apply (Similar to Inner Join)
  • ⇒ Outer Apply (Similar to Outer Join)
. Example

Sub Queries

Definition

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

.

Flavors

  • 1) Simple subquery – Runs once with the result being plugged in to the outer query. Subqueries are executed first followed by the outer query.
  • .
  • 2) Correlated Subquery – References the outer query. The outer query is executed first followed by the subquery being executed once for each row in the outer query.

.

Fundamentals

  • ⇒ Also called Inner Query or Inner Select
  • ⇒ Upto 32 levels of nesting is possible
  • ⇒ DISTINCT keyword cannot be used with GROUP BY
  • ⇒ ORDER BY can only be specified when TOP is also specified
  • ⇒ COMPUTE, For Browse and INTO clauses cannot be specified.
  • ⇒ Views created using a subquery cannot be updated.
  • ⇒ ntext, text, and image data types cannot be used in the select list

.

[ For more detail ]

Aggregating Data

Numbers the output of a result set sequentially.
.
Syntax
ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , … [ n ] ] order_by_clause )
.
Sample
. [ For more detail ] .
Distributes the rows in an ordered partition into a specified number of groups.
.
Syntax
NTILE (integer_expression) OVER ( [ ] < order_by_clause > )
.
Sample
. [ For more detail ] .
Returns the rank of each row within the partition of a result set.
.
RANK
The rank of a row is one plus the number of ranks that come before the row in question.
.
DENSE_RANK
The rank of a specific row is one plus the number of distinct rank values that come before that specific row.
.
Syntax
RANK ( ) OVER ( [ partition_by_clause ] order_by_clause ) DENSE_RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )
.
Sample
. [ For more detail ] .
Get a sample set of random data from the table. Data is read at a page level (i.e page by page, not row by row)

.

Syntax
… FROM tableName TABLESAMPLE (num PERCENT)
… FROM tableName TABLESAMPLE (num ROWS)

.

Sample

.

Sum: The sum of all the values in a column.
Avg: The average value for a numeric column.
Min: The smallest value in a column.
Max: The largest value in a column.
StDevP: The standard deviation for the population
StDev: The statistical standard deviation of all values in the specified expression
VarP: The statistical variance for the population for all values in the specified expression.
Var: The statistical variance of all values in the specified expression
. .

Distributed Queries

Query 1

EXEC sp_addlinkedserver @server = ‘serverName’, @srvproduct = ‘SQL Server’

. Query 2

EXEC sp_addlinkedserver @server = ‘serverName’, @datasrc=’Source-Name’, @srvproduct =”, @provider = ‘SQLOLEDB’

. View

SELECT [Name], Product, Provider, Data_Source FROM sys.servers WHERE Is_Linked = 1;

. Usage

SELECT *FROM [serverName].master.sys.databases

. Delete

EXEC sp_DropServer @server = ‘serverName’;

.

Query 1

EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘server-name’,
@useself = ‘false’,
@locallogin = ‘local-login’,
@rmtuser = ‘user-name’,
@rmtpassword = ‘password’

.

Query 2

EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘ServerName’,
@useself = ‘false’

.

Delete

EXEC sp_droplinkedsrvlogin ‘Name’, NULL

.

Examples

Query 1
SELECT * FROM OPENQUERY(LinkedServerName, ‘SELECT *FROM Products WHERE ProductName = “Radio”‘);
.
Query 2
UPDATE OPENQUERY(LinkedServerName, ‘SELECT *FROM Products WHERE ProductName = “Radio”‘) SET [PageName_Or_RangeName] = ‘Data’ WHERE ProductName = “Radio”;
.
Query
.
View Log
Win + R > dcomcnfg > Component Services > My Computer > Distributed Transaction Coordinator > Transaction Statistics
.

Triggers

A special type of stored procedure that automatically runs when an event occurs in the database server.

.

DML Trigger

Runs when a user tries to modify data through a DML event.

events: INSERT, UPDATE, or DELETE

.

DDL Trigger

Runs in response to a variety of DDL events.

events: CREATE, ALTER, DROP etc.

.

Logon Trigger

Fires in response to the LOGON event that’s raised when a user’s session is being established.

.

[ For more detail ]

Create/Alter . Delete

DROP TRIGGER trigger_name

. Enable/Disable

Alter Table table_name Enable|Disable Trigger trigger_name

.

Enable/Disable Nested Triggers (Server Wide)

EXEC sp_configure ‘Nested Triggers’, 1 Reconfigure

.

Enable/Disable Recursive Triggers (Database Wide)

ALTER DATABASE dbName SET RECURSIVE_TRIGGERS ON

. [ For more detail ]

Error Handling

System Error Messages

SELECT * FROM sys.messages SELECT * FROM Master.dbo.SysMessages

.

Indexing

An on-disk structure associated with a table or view that is used in relational databases to improve query performance, faster data retrieval, table fragmentation etc.

.

  • ⇒ Index contains keys built from one or more columns
  • ⇒ Keys are stored in a structure (B-tree)

.

Index Types

  • ⇒ Hash
  • ⇒ Clustered
  • ⇒ Nonclustered
  • ⇒ memory-optimized Nonclustered
  • ⇒ Unique
  • ⇒ Columnstore
  • ⇒ Index with included columns
  • ⇒ Index on computed columns
  • ⇒ Filtered
  • ⇒ Spatial
  • ⇒ XML
  • ⇒ Full-text

[ For More detail ]

.

Clustered Index

Sort and store the data rows in the table or view based on their key values.

.

Nonclustered Index

Contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

.

Clustered Index |
Nonclustered Index

Guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique.

. [ For more detail ]

An optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data.

. Advantages over full-table indexes
  • ⇒ Improved query performance and plan quality
  • ⇒ Reduced index maintenance costs
  • ⇒ Reduced index storage costs
. [ For more detail ]
. [ For more detail ]

JSON Manipulation

XML Manipulation

Retrieve the results of a SQL query as XML

. Modes
  • ⇒ RAW : Generates a single row element per row in the rowset.
  • ⇒ AUTO : Generates nesting in the resulting XML by using heuristics
  • ⇒ EXPLICIT : Allows more control over the shape of the XML
  • ⇒ PATH : Provides simpler way for additional nesting of complex properties.
. Samples . [ For XML | Raw | Auto | Explicit | Path ] .

Common Practices

.