T-SQL Tutorial For Beginners

Welcome back to another beginner tutorial. In my Previous tutorial I have explained you about basic SQL. In this tutorial, I will give you brief about Transact-SQL(T-SQL).

These are some differences as well as similarities between T-SQL and SQL. T-SQL is an enhanced version of SQL. In T-SQL we have SQL along with programming concepts.(SQL + Programming = T-SQL). T-SQL have most of the programming concepts like variables, loops, conditions, etc..
Note: Oracle as has the similar concept known as PL/SQL

1) Variables:

Simply variable is buckets where you can store values. T-SQL support variables. You can use a variable to assign values, change values when you need. In T-SQL variables declare, assign values, retrieving values as below as below.

--Syntex: 
---declaration 
DECLARE @variable data-type  
---Assignment 
SET @variable='value'  
---Retrieve 
SELECT @variable  
--Example 
DECLARE @MyName varchar(20)  
SET @MyName='Aravind'  
SELECT @MyName 

2) GO

When we execute SQL statement the will execute one by one. But in T-SQL we have a facility to execute a bunch of statement at a time. Known as Batch Execution. The main use of this is performance. Also if any one statement has syntactical error in any one statement, none of the statement will execute.

--example 
USE StudentDb  
INSERT INTO Student(name,email) VALUES ('aravind','aravind@techumber.com');  
SELECT * FROM Student;  
GO 

3) IF....ELSE

In programming world IF....ELSE is the basic conditional control. Have a look at the below example how we can use IF....ELSE in T-SQL.

DECLARE @temp char(2)  
SELECT @temp = Gender  
FROM Student  
WHERE StudentId=333  
IF @temp='M'  
PRINT 'Hi Handsome. How are you today?'  
ELSE  
PRINT 'Hi beautiful. How are you today?'  

4) BEGIN...END

BEGIN, END keyword are much like open and close parenthesis we use in high-level programming languages. Mostly we use these in condition statement where we have more than one statement to execute. See the example

IF (SELECT COUNT(*) FROM Student WHERE Gender = 'M') > 0  
BEGIN  
PRINT 'This is a boys college'  
PRINT ' '  
SELECT fname, lname  
FROM Student  
END  
ELSE  
PRINT 'No boys'  

5) WHILE

WHILE is used to repeatedly execute statements until the condition became false. There are many advantages of using while in T-SQL. See this example.

DECLARE @counter INT,@total INT  
SET @counter=0  
SET @total=SELECT COUNT(*) FROM Student WHERE lname="Gates"  
WHILE (@counter < @total)  
BEGIN  
UPDATE Student  
SET lname= lname + CAST(@counter as char(2))  
WHERE lname="Gates"  
SET @counter = @counter+1  
END 

In this example, we finding all record with the last name as Gates and updating them with count appending to them.

6) CASE

In programming, we use case instead of multiple nested if..else statements. Similarly, we use CASE to evaluate an express whether it is true or false based on it uses specific values.

SELECT latitude , 'For Country' =  
CASE  
WHEN 'AF' THEN 'Afghanistan'  
WHEN 'IN' THEN 'India'  
WHEN 'RU' THEN 'Russia'  
WHEN 'US' THEN 'United States'  
WHEN 'LK' THEN 'Sri Lanka'  
END  
FROM Country 

7) Transactions

Transactions are much like a batch. The only difference between Transaction and batch is we can undo all changes we made by statements within the single translations. We can not do this in Batch. We use COMMIT TRANSACTION or ROLLBACK TRANSACTION statement at the end. If we put ROLLBACK TRANSACTION it will undo all the changes.

BEGIN TRANSACTION  
SELECT * FROM Student  
...... 
COMMIT TRANSACTION 

8) Stored Procedures

Stored Procedures are a most Important concept in databases. There are many advantages using stored procedures. Mainly performance because sorted procedures are compiled and will be cached after the first time we used it. Another big advantage is we can call stored procedures from programming languages like vb,c#.

CREATE PROCEDURE uspGetStudent  
@sname nvarchar(50) 
AS  
SELECT fname,lname,rolno  
FROM Student  
WHERE fname = @sname  
GROUP BY fname  
ORDER BY fname  
GO 

--Execute stored procedure 
EXEC usp 'aravind'  
GO 

Conclusion

These are very basic concepts of T-SQL. There are some advanced concepts like Views,Triggers,etc...

Get posts by email

Like what you're reading? Get these posts delivered to your inbox.