T-SQL Cheatsheet

TSQL Cheatsheet
Share:

About

This short post is a cheat sheet for transact SQL or T-SQL for Microsoft Server. I wrote this for myself so if I ever have trouble remembering some of the more common queries(as I don’t work with databases on a daily basis) I can just look them up on my own site. And maybe someone out there on the internet will find this useful for themselves as well. 

Note: I covered some of the more common stuff here. If you want a cheatsheet/reference that covers everything in more detail I recommend you check out this great resource here.

SQL Statements To Create, Delete, Modify, Fill and Empty a Table

/********************** Create a table ***********************/

CREATE TABLE nameOfYourTable( 
   column1 datatType, /*for example: varchar, text, int, numeric, binary, ...*/
   column2 dataType, 
   column3 dataType, 
   PRIMARY KEY(nameOfTheKeyColumn)
);

/*************************************************************/


/********************* Modify the table **********************/

ALTER TABLE nameOfYourTable ALTER COLUMN nameOfTheColumn dataType

/*************************************************************/


/***************** Insert data into a table ******************/

INSERT INTO nameOfYourTable (column1, column2, column3)
VALUES (value1, value2, value3); 

/*************************************************************/


/***************** Update data in the table ******************/

UPDATE nameOfYourTable 
SET column1 = yourNewValue, column2 = yourNewValue 
WHERE condition;

/*************************************************************/


/***************** Delete data from a table ******************/

DELETE FROM nameOfYourTable WHERE condition

/*************************************************************/


/**************** Delete all data in a table *****************/

TRUNCATE TABLE nameOfYourTable

/*************************************************************/


/********************* Delete the table **********************/

DROP TABLE nameOfYourTable

/*************************************************************/

Data Querys

/***************** Query data from a table *******************/

SELECT column1 FROM nameOfYourTable

/*************************************************************/


/*************** Query data with a condition *****************/

SELECT column1 FROM nameOfYourTable WHERE condition

/*************************************************************/


/******************* Order and group data ********************/

SELECT column1, column2, column3 FROM nameOfYourTable WHERE condition 
ORDER BY column1ASC ASC /*ASC or DESC*/

SELECT column1, column2 FROM nameOfYourTable WHERE conditions
GROUP BY column1 
ORDER BY column1 ASC /*ASC or DESC*/

/*************************************************************/

Joins

I only added an example of an inner join. And instead of writing out all the other types of joins, I have added this lovely graphic I have found which shows you all the different joins. 

/*************** Copy one table into another *****************/ 

SELECT * FROM firstTable INNER JOIN secondTable ON firstTable.key = secondTable.key

/*************************************************************/
sql joins
Source: https://sql.sh/wp-content/uploads/2014/06/sql-join-infographie.png

Aggregate Functions

/***************** Aggregate functions ******************/

/* Functions: COUNT(), AVG(), SUM(), MIN(), MAX()*/

SELECT AVG(column1) FROM nameOfYourTable

/*HAVING must be used for the condition instead of WHERE when aggregate functions used.*/
SELECT column1 FROM nameOfYourTable HAVING SUM(column1) > 20

SELECT column1 FROM nameOfYourTable
GROUP BY column1
HAVING MAX(column1) > 20;

/*************************************************************/

Constraints

/********************** Table constraints ***********************/

CREATE TABLE nameOfYourTable (
    PKID int NOT NULL PRIMARY KEY,  /*defines primary key*/
	column2 int NOT NULL,			/*can't be null*/
	column3 int UNIQUE,				/*each entry will have to be unique*/
    FKID int FOREIGN KEY REFERENCES nameOfYourSecondTable(primaryKeyOfSecondTable) /*defines foreign key and links it to the primary key of another table*/
);

/*************************************************************/


/********************* Create an index ***********************/

CREATE INDEX indexName ON nameOfYourTable (column1, column2, ...)

/*************************************************************/

Transactions And Error Handling

/********************** Error handling ***********************/

BEGIN TRY
	/*
		Your queries...
	*/
END TRY
BEGIN CATCH
	/*
		Your error handling code...
	*/
	/*For example, if an error occurs you can retrieve the information about it.*/
	SELECT   
		ERROR_NUMBER() AS ErrorNumber,   
		ERROR_SEVERITY() AS ErrorSeverity,   
		ERROR_STATE() AS ErrorState,   
		ERROR_PROCEDURE() AS ErrorProcedure,   
		ERROR_LINE() AS ErrorLine,   
		ERROR_MESSAGE() AS ErrorMessage;   
END CATCH;

/*************************************************************/
/*********************** Transactions ************************/

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO nameOfYourTable(column1, column2, column3) VALUES (val1, val2, val3); 
    INSERT INTO nameOfYourTable(column1, column2, column3) VALUES (val1, val2, val3);  
    ...

	/*If everything within the transaction block executes seccesfully the changes will be saved.*/
    COMMIT TRANSACTION; /*Else a transaction will be in the uncommited state.*/
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION; /*Revert to the previous state if an error occoured.*/
END CATCH;

/*************************************************************/


/************** Reading Uncommited Transactions **************/

--By default noncommited transactions can't be read.
--You can chanage this with the following statement: 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*************************************************************/

Control Of Flow

/******************** IF ELSE Statement **********************/

IF (yourCondition)
   – your code ...
ELSE IF (yourCondition)
   – your code ...
ELSE
   – your code ...
END

/*************************************************************/


/******************** SWITCH Statement ***********************/

SELECT  
	CASE column1
		  WHEN 1 THEN PRINT 'one' – PRINT simply prints the value to the console.
		  WHEN 2 THEN PRINT 'two'
		  WHEN 3 THEN PRINT 'three'
		  ELSE
	END
FROM nameOfYourTable


/*************************************************************/


/******************* WHILE LOOP ********************/

WHILE yourCondition
BEGIN – BE
   – your code ...
   
   BREAK – Can be used to break out of the loop.
   CONTINUE – Can be used to skip any further code in the current iteration of the loop and start the next loop.
END

/*************************************************************/

/******************* GO TO Statement ********************/

GOTO section2 – Code will execute from the "section2:" label.  

section1:  
    – your code ... 
section2:  
    – your code ...

/*************************************************************/


/*************************** THROW ***************************/

IF(yourCondition)
BEGIN  
	PRINT 'Error';
	THROW – Used to raise an exception.
END 

/*************************************************************/

Other

String Functions
/********************* String Functions ***********************/

--SUBSTRING(string, start, length)
SELECT SUBSTRING('Some string', 3, 6) – Output: me str

 –          (string, length)
SELECT LEFT ('Some string', 3)		  – Output: Som
SELECT RIGHT('Some string', 3)		  – Output: ing

--Gives length of the string.
LEN('Some string')

/*************************************************************/
Data Type Conversion
/******************* DATA TYPE CONVERSION ********************/

CAST('5' AS int)
CAST(5 AS varchar)
CAST('9/13/2022' AS DATETIME)
...

--If cast fails TRY_CAST returns NULL instead of throwing an error.
IF(TRY_CASTCAST('5' AS int) IS NULL)
  – Do something to handle failed cast.
ELSE
  – Continue as normal.

--NOTE: CONVERT and TRY_CONVERT can also be used for data type conversion.

/*************************************************************/
Variables
/*********************** Variables ***************************/

DECLARE @myString VARCHAR(20);
SET @myString = 'Some string';

PRINT @TestVariable

/*************************************************************/
Copy a Table
/*************** Copy one table into another *****************/

SELECT * INTO nameOfYourTable FROM nameOfYourCopiedTable

/*************************************************************/
Stored Procedures
/******************* Making a stored procedure ********************/

/** Make sure the stored procedure doesn't already exist. **/  
IF OBJECT_ID ( 'myStoredProcedure', 'P' ) IS NOT NULL    
    DROP PROCEDURE myStoredProcedure   

GO – Execute the previous section of code

/** Create new stored procedure. **/  
CREATE PROCEDURE myStoredProcedure AS 
	SELECT * FROM nameOfYourTable

GO

--Call stored procedure
EXEC myStoredProcedure @column = 'nameOfYourColumn';

/*************************************************************/
Create User And Login
/****************** Create Login And User ********************/

CREATE LOGIN yourloginname WITH password='yourpassword';  
GO 

CREATE USER yourusername FOR LOGIN yourloginname 
GO 

EXEC sp_addrolemember N'db_ddladmin', N'yourusername' 
GO 

--Note: If error "User must be in the master database." add this: 
USE master 
GO 

/*************************************************************/
Get Open Sessions
If you are using the Azure SQL server serverless database you might run into the problem where your DB won’t automatically shut down when there is no traffic and as a result run up your cost. If your Azure database isn’t going to sleep there is a possibility that a connection was left open which is preventing it from doing so. Using the queries below you can see all the open sessions and close them if needed.
/************** Get open sessions to Azure DB ****************/

SELECT session_id, host_name, program_name, client_interface_name, login_name, status, login_time, last_request_start_time, last_request_end_time FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_resource_governor_workload_groups AS wg ON s.group_id = wg.group_id WHERE s.session_id <> @@SPID AND ( ( wg.name like 'UserPrimaryGroup.DB%' AND TRY_CAST(RIGHT(wg.name, LEN(wg.name) - LEN('UserPrimaryGroup.DB') - 2) AS int) = DB_ID() ) OR wg.name = 'DACGroup' );

--Close a session
KILL processID 

/*************************************************************/

Share:

Leave a Reply

Your email address will not be published.

The following GDPR rules must be read and accepted:
This form collects your name, email and content so that we can keep track of the comments placed on the website. For more info check our privacy policy where you will get more info on where, how and why we store your data.

Advertisment ad adsense adlogger