SQLServer TSQL CheatSheet

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)