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 inner join example and added this lovely graphic I have found that shows you all the other types of joins.
/*************** Copy one table into another *****************/ SELECT * FROM firstTable INNER JOIN secondTable ON firstTable.key = secondTable.key /*************************************************************/
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
/********************** 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
/*********************** 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
/* 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 /*************************************************************/
Functions
/*********************** Functions ***************************/
CREATE FUNCTION Addition (@a int, @b int) /*function name and parameters*/
RETURNS int /* return type */
AS
BEGIN /* Code to be executed ... */
DECLARE @result int;
SET @result = @a + @b;
RETURN(@result)
END
GO
/*Call function.*/
PRINT dbo.Addition(5, 5);
/*************************************************************/ Dynamic SQL
/********************** Dynamic SQL **************************/ /*Declare variables.*/ DECLARE @id int; DECLARE @query nvarchar; /*Set the variable. If this was put in a function the parameter could be used here instead.*/ SET @id= '10'; /*Build query string.*/ SET @query = 'SELECT * FROM yourTableName WHERE ID = ' + @id; /*Execute dynamic SQL.*/ EXEC sp_executesql @query; /*************************************************************/
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';
/*************************************************************/ Triggers
/************************** Triggers **************************/
/** Make a new tigger. **/
CREATE TRIGGER yourTriggerName ON nameOfTheTable
AFTER /*FOR | AFTER*/ DELETE
AS
/*Code that executes on trigger.*/
BEGIN
/*This code inserts the ID of the deleted row into the table DeletedRows so we can keep track of what was deleted(there are better ways of doing this but this is just an example).*/
DECLARE @ID INT;
SELECT @ID = ID FROM DELETED;
INSERT INTO DeletedRows(id) VALUES (@ID);
END;
/* Delete trigger. */
DROP TRIGGER IF EXISTS yourTriggerName;
/*************************************************************/ Job Scheduling
/*********************** Job Scheduling ***********************/
/* For example, this job will run once a day at 22:00 and it will delete all of the entries older than 24h. */
USE msdb;
GO
/*Define a job.*/
EXEC sp_add_job
@job_name = N'DeleteOlderThan24',
@description = N'Deletes records older than 24h.';
GO
/*Define a job step. Multiple Steps can be added for a job.*/
EXEC sp_add_jobstep
@job_name = N'DeleteOlderThan24',
@step_name = N'DeleteOld_1',
@subsystem = N'TSQL',
@command = N'DELETE FROM nameOfYourTable WHERE dateColumn < DATEADD(hour, -24, GETDATE());',
@retry_attempts = 1,
@retry_interval = 1;
GO
/*Make a schedule.*/
EXEC sp_add_schedule
@schedule_name = N'RunDailyAt22',
@freq_recurrence_factor = 1,
@active_start_time = 220000; /*Runs at 22:00:00*/
GO
/*Attach a job to the schedule.*/
EXEC sp_attach_schedule
@job_name = N'DeleteOlderThan24',
@schedule_name = N'RunDailyAt22';
GO
/*Assign the job to a server.*/
EXEC sp_add_jobserver
@job_name = N'DeleteOlderThan24',
@server_name = N'LOCAL';
GO
/* Delete job, job step, schedule and jobserver. */
EXEC sp_delete_job @job_name = 'DeleteOlderThan24';
EXEC sp_delete_jobstep @job_name = 'DeleteOlderThan24', @step_id = 1; --First step is removed.
EXEC sp_detach_schedule @job_name = 'DeleteOlderThan24';
EXEC sp_delete_jobserver @server_name = N'LOCAL', @job_name = 'DeleteOlderThan24';
/*************************************************************/
Creating Users And Logins
/****************** 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 /*************************************************************/
User Permissions
You can see more about permissions in the official MS documentation here.
/*********************** Permissions **************************/ /*Give the user "someUsername" the permission to use INSERT on table "someTable".*/ GRANT INSERT ON someTable TO someUsername; /*Deny the user "someUsername" the permission to use ALTER on table "someTable".*/ DENY ALTER ON someTable TO someUsername; /*************************************************************/
User Roles
You can assign users to existing roles(see all the possible roles in the official MS documentation here). Or you can define your own roles with the privileges that you specify.
/************************* Roles ****************************/ /*Create your own custom role.*/ CREATE ROLE yourRoleName; /*Grant the privilege to use SELECT on table "someTable" if a user is part of role "yourRoleName".*/ GRANT SELECT ON someTable TO yourRoleName; /*Deny the privilege to change data of table "someTable" if a user is part of role "yourRoleName".*/ DENY ALTER. ON someTable TO yourRoleName; /*Add role to a member*/ ALTER ROLE yourRoleName ADD MEMBER yourUserName /*Remove role of a member*/ ALTER ROLE yourRoleName DROP MEMBER yourUserName /*************************************************************/
Schemas
A schema is a group or collection of database items like tables, triggers, stored procedures, … This allows for a better organization of a database.
/************************ Schemas **************************/ /*Create a schema.*/ CREATE SCHEMA MySchema /*Delete a schema.*/ DROP SCHEMA MySchema /*Create a table on a schema*/ CREATE TABLE MySchema.MyTable(ID int, data nvarchar); /*Read from a table from a certain schema.*/ SELECT * FROM MySchema.MyTable; /*************************************************************/
File Groups
File groups can be used to split a database into multiple parts that can then be stored in different locations/different disks. This can improve performance. By default, everything is put on the PRIMARY filegroup. More about file groups here.
/*********************** File Groups *************************/
/*Create file group fg1 for database yourDatabse.*/
USE yourDatabse
GO
ALTER DATABASE yourDatabse
ADD FILEGROUP fg1
GO
/*Add files to file group fg1 for database yourDatabse.*/
ALTER DATABASE yourDatabse ADD FILE
(
NAME = N'file1',
FILENAME = N'C:\SQLFG\file1.ndf',
SIZE = 5 MB,
MAXSIZE = 10MB,
FILEGROWTH = 1 MB
)
TO FILEGROUP fg1
GO
/*Add second file to file group.*/
ALTER DATABASE yourDatabse ADD FILE
(
NAME = N'file2',
FILENAME = N'C:\SQLFG\file2.ndf',
SIZE = 5 MB,
MAXSIZE = 10MB,
FILEGROWTH = 1 MB
)
TO FILEGROUP fg1
GO
/*Create a table on the fg1 filegroup.*/
CREATE TABLE yourFirstTable(column1 nvarchar, column2 nvarchar) ON fg1;
GO
/*************************************************************/ Bulk Insert
/*********************** Bulk Insert *************************/ BULK INSERT MyTable FROM 'C:\Users\SomeUser\Desktop\fileToImport.sql'; /*************************************************************/
SQLCMD
You can download this tool here here.
sqlcmd can be used to run queries on the SQL server from the command line. This can be useful for scripting or for processing very large files. For example, if you try to open a very large(around 1GB or more) .sql file in MSSQL Studio it tends to freeze up and you won’t be able to run the script. In such a case the sqlcmd can be used, see the example below(replace text and quote marks with your information).
sqlcmd -S "ip or domain of the SQL server" -U "username" -P "password" -i "your SQL script file".sql
Indexes
/************************** Indexes ***************************/ /*Clustered Index*/ /*Only one can be created as the data is part of the index structure*/ CREATE CLUSTERED INDEX yourIndexName ON yourTableName (column); /*Non-Clustered Index*/ /*Multiple can be created as the index structure doesn't contain the data but only the references to the data itself.*/ CREATE NONCLUSTERED INDEX yourIndexName ON yourTableName (column); /*You can build the index over multiple columns (column1, column2, ...)*/ /*************************************************************/
SSMS Query Analysis Tools
SSMS(SQL Server Management Studio) provides you with tools to analyze the query execution plan and performance so you can then optimize it. These tools are available in thge Query drop down menu.
Let’s see an example for this query.
SELECT * FROM person.Person WHERE FirstName = 'Bob';
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
/*************************************************************/
