SQL Server T-SQL Cheat Sheet

SQLServer TSQL CheatSheet

DECLARE and SET Varibales

DECLARE @Mojo int
SET @Mojo = 1
SELECT @Mojo = Column FROM Table WHERE id=1

IF / ELSE IF / ELSE Statement

IF @Mojo < 1
  BEGIN
	PRINT 'Mojo Is less than 1'
  END
ELSE IF @Mojo = 1
  BEGIN
    PRINT 'Mojo Is 1'
  END
ELSE
  BEGIN
	PRINT 'Mojo greater than 1'
  END

CASE Statement

SELECT Day = CASE 
  WHEN (DateAdded IS NULL) THEN 'Unknown'
  WHEN (DateDiff(day, DateAdded, getdate()) = 0) THEN 'Today'
  WHEN (DateDiff(day, DateAdded, getdate()) = 1) THEN 'Yesterday'
  WHEN (DateDiff(day, DateAdded, getdate()) = -1) THEN 'Tomorrow'
  ELSE DATENAME(dw , DateAdded) 
END
FROM Table

Add A Column

ALTER TABLE YourTableName ADD [ColumnName] [int] NULL;

Rename a Column

EXEC sp_rename 'TableName.OldColName', 'NewColName','COLUMN';

Rename a Table

EXEC sp_rename 'OldTableName', 'NewTableName';

Add a Foreign Key

ALTER TABLE Products WITH CHECK 
ADD CONSTRAINT [FK_Prod_Man] FOREIGN KEY(ManufacturerID)
REFERENCES Manufacturers (ID);

Add a NULL Constraint

ALTER TABLE TableName ALTER COLUMN ColumnName int NOT NULL;

Set Default Value for Column

ALTER TABLE TableName ADD CONSTRAINT 
DF_TableName_ColumnName DEFAULT 0 FOR ColumnName;

Create an Index

CREATE INDEX IX_Index_Name ON Table(Columns)

Check Constraint

ALTER TABLE TableName
ADD CONSTRAINT CK_CheckName CHECK (ColumnValue > 1)

DROP a Column

ALTER TABLE TableName DROP COLUMN ColumnName;

Single Line Comments

SET @mojo = 1 --THIS IS A COMMENT

Multi-Line Comments

/* This is a comment
	that can span
	multiple lines
*/

Try / Catch Statements

BEGIN TRY
	-- try / catch requires SQLServer 2005 
	-- run your code here
END TRY
BEGIN CATCH
	PRINT 'Error Number: ' + str(error_number()) 
	PRINT 'Line Number: ' + str(error_line())
	PRINT error_message()
	-- handle error condition
END CATCH

While Loop

DECLARE @i int
SET @i = 0
WHILE (@i < 10)
BEGIN
	SET @i = @i + 1
	PRINT @i
	IF (@i >= 10)
		BREAK
	ELSE
		CONTINUE
END

CREATE a Table

CREATE TABLE TheNameOfYourTable (
  ID INT NOT NULL IDENTITY(1,1),
  DateAdded DATETIME DEFAULT(getdate()) NOT NULL,
  Description VARCHAR(100) NULL,
  IsGood BIT DEFAULT(0) NOT NULL,
  TotalPrice MONEY NOT NULL,  
  CategoryID int NOT NULL REFERENCES Categories(ID),
  PRIMARY KEY (ID)
);

User Defined Function

CREATE FUNCTION dbo.DoStuff(@ID int)
RETURNS int
AS
BEGIN
  DECLARE @result int
  IF @ID = 0
	BEGIN
		RETURN 0
	END
  SELECT @result = COUNT(*) 
  FROM table WHERE ID = @ID
  RETURN @result
END
GO
SELECT dbo.DoStuff(0)

Questions, comments, criticism, or requests can be directed Here