SQL Queries

Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate

The general formula is here

Alter table ForeignKeyTable add constraint ForeignKeyTable_ForiegnKeyColumn_FK 

FOREIGN KEY (ForiegnKeyColumn) references PrimaryKeyTable (PrimaryKeyColumn)

Altering an existing column to add a default constraint:

ALTER TABLE { TABLE_NAME }

ADD CONSTRAINT { CONSTRAINT_NAME }

DEFAULT { DEFAULT_VALUE } FOR { EXISTING_COLUMN_NAME }

Adding a new column, with default value, to an existing table:

ALTER TABLE { TABLE_NAME } 

ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL } 

CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE }

The general formula for adding check constraint in SQL Server:

ALTER TABLE { TABLE_NAME }

ADD CONSTRAINT { CONSTRAINT_NAME } CHECK ( BOOLEAN_EXPRESSION )

To drop a constraint

ALTER TABLE { TABLE_NAME } 

DROP CONSTRAINT { CONSTRAINT_NAME }

Identity column

Create Table tblPerson

(

PersonId int Identity(1,1) Primary Key,

Name nvarchar(20)

)

SET Identity_Insert tblPerson ON

SET Identity_Insert tblPerson OFF

Reset the identity column after all the rows been deleted

DBCC CHECKIDENT(tblPerson, RESEED, 0)

SCOPE_IDENTITY() – returns the last identity value that is created in the same session and in the same scope.

@@IDENTITY – returns the last identity value that is created in the same session and across any scope.

IDENT_CURRENT(‘TableName’) – returns the last identity value that is created for a specific table across any session and any scope.

To create the unique key using a query:

Alter Table Table_Name

Add Constraint Constraint_Name Unique(Column_Name)