Transact-SQL

DDL | DML | DCL | Sys Funct | Goto | Waitfor | While | If-Else

Database Object
1. Table
2. Store procedure
3. Triggers
4. View
5. Indexes

All are subset of Transact-SQL

Data Definition Language (DDL)
- is used to create and manage db and db's object
1. Create table
2. Drop table
3. Alter table
4. Truncate // DDL , cant' roll back , id reset to 1, faster , no WHERE use, can't fire trigger

eg..
-Create Table customers ( name varchar(20) )
-Alter Table customers Add birthdate datatime After name

Back

Data Manipulation Language (DML)
- is used to retrieve, insert, modify and delete from db
1. Select
2. Insert
3. Update
4. Delete / truncate

Back

Data Control Language ( DCL)
-is used to manage the db security
1. Grant Permission ON Object To user
2. Deny
3. Revoke

eg.
-Grant Select ON Categories TO user1

Listing security info of DB using SP
-Exec sp_helprotect ' parameter' // p: obj or statement

Back

System Functions ( select @@servername)
-@@servername
-@@connections
-@@identity
-@@rowcount

Back

Control Flow

IF-Else
IF (condition)
begin
statement 1
statement 2
end

ELSE

statement1

Back

WAITFOR
-Waitfor Time '08.00.00' // wait till 8am
-Waitfor Delay '00.01.00; // wait for 1 min after exe 08.01.00

Back

WHILE
-exec till condition is false

Declare @a int, @b int, @result int
SET @a=3
SET @b=4
SET @result=0

While (@b>0)
Begin
Set @result+=@a
set @b=@b-1
end

Back

GOTO
-Direct to where Label is placed

eg

If Not exists( selct * from suppliers)
GOTO no_rows
else
GOTO finish

no_rows:
print 'an error occured'

finish:
print ' the program contains some rows'

Back