Show Menu
Cheatography

123df d adf a dfadsf adf

Stored Procedure

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

Add not null column to existing table

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

drop column

ALTER TABLE table_name
DROP COLUMN column_name;
 

Check Exists

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 temp table

DROP TABLE IF EXISTS #Results

If else

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

Merge

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)
 

Check if table is existed (Using OBJECT_ID)

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

add foreign key

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]
   )
;

add primary key

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 unique constrant

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

create index

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

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet
          Essential MySQL Cheat Sheet
          Oracle SQL Developer Keyboard Shortcuts