T-SQL Cheat Sheet by giangpdh

CREATE OR ALTER PROCEDURE dbo.usp_Name(@param)
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  BEGIN TRANSACTION
  COMMIT
  SET XACT_ABORT OFF;
  SET NOCOUNT OFF;
END

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'MyNewColumn' AND Object_ID = Object_ID(N'[dbo].[MyTable]'))
BEGIN
	ALTER TABLE MyTable
        ADD MyNewColumn INT NOT NULL DEFAULT 0
END

ALTER TABLE table_name
DROP COLUMN column_name;

SELECT *,  ROW_NUMBER() OVER(PARTITION BY TenantId ORDER BY Id)  from dbo.[Test] WHERE [Name] is null

IF [NOT] EXISTS ( SELECT 1 FROM MyTable WHERE ... )
     <do smth>

IF NOT EXISTS ( SELECT 1 FROM Users WHERE FirstName = 'John' AND LastName = 'Smith' )
BEGIN
    INSERT INTO Users (FirstName, LastName) VALUES ('John', 'Smith')
END

DROP TABLE IF EXISTS #Results

IF Boolean_expression   
     { sql_statement | statement_block }   
[ ELSE   
     { sql_statement | statement_block } ]

MERGE <target_table> AS tgt
USING <table_source> AS src
ON <merge_search_condition> 
WHEN MATCHED THEN <merge_matched>
WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>
WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

MERGE Production.UnitMeasure AS tgt  
USING (SELECT @UnitMeasureCode, @Name) as src (UnitMeasureCode, Name)  
ON (tgt.UnitMeasureCode = src.UnitMeasureCode)  
WHEN MATCHED THEN
	UPDATE SET Name = src.Name  
WHEN NOT MATCHED THEN  
	INSERT (UnitMeasureCode, Name)  
	VALUES (src.UnitMeasureCode, src.Name)

CREATE [ OR ALTER ] FUNCTION <function_name> (<parameters>)
RETURNS TABLE
AS
RETURN
(
  <select_statement>
);

CREATE [ OR ALTER ] FUNCTION <function_name> ([ parameter ])
RETURNS @return_variable TABLE <table_type_definition>
BEGIN
  <function_body>
  RETURN
END

IF OBJECT_ID(N'dbo.<table_name>', N'U') IS NOT NULL
BEGIN
    PRINT 1
END

CREATE TABLE <schema>.<table_name>
   (
      <column_name> int NOT NULL
      , CONSTRAINT FK_<table_name>_<reference_table_name> FOREIGN KEY (<column_name>)
        REFERENCES <reference_table_name> (<reference_column_name>)
        [ON DELETE CASCADE]
        [ON UPDATE CASCADE]
   )
;

CREATE TABLE <schema>.<table_name>   (
      <column_name> int IDENTITY (1,1) NOT NULL
      , CONSTRAINT PK_<table_name>_<column_name> PRIMARY KEY CLUSTERED (<column_name>)
   )

CREATE TABLE <table_name> 
(  
   <column_name> int NOT NULL,  
   CONSTRAINT UC_<column_name> UNIQUE(<column_name>)  
)

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

CREATE TABLE <schema_name>.<table_name>
(
    INDEX IX_<table_name>_<column_name> NONCLUSTERED (<column_name>)
)