T-SQL in SQL Server

In SQL Server we basically write two types of Commands one is individual SQL Statements like SELECT, INSERT, UPDATE , DELETE , TRUNCATE, MERGE, ALTER , DROP etc. and second is a batch or set of SQL statement called T-SQL (Transact SQL). T-SQL mainly used for implementing complex business logic. This set of SQL Command executes in a Batch. It means the complete set of code goes once in to the SQL engine, compiles, and produces the Output. In contrast of batch the individual statements make separate round trip from server to client and produce output.

Here execute this below two select statement in one go.

select * from Tbl0
GO
select * from Tbl1
GO

Again execute the same two select statement in different way like given below.

begin
select * from Tbl0
select * from Tbl1
end

In both the scenario output is same, but in first case the two "select" statement make separate round trip to SQL Engine one by one where as in second case the complete batch goes once to SQL Engine and produce output. In second case the statement is written in a T-SQL batch

Example of T-SQL batch

DECLARE
      --Declare Variable / Cursor / Type
BEGIN
      --Implement your Business Logic here
END

 Here "DECLARE" is optional, this is used for declaring variables, cursors, and types. In case if we don’t want to declare any variable may omit this. BEGIN and END is the boundary where we simply implement our Logic to perform.
We can have two types of Block in T-SQL.

1) Anonymous Block : A unnamed block of code that doesn’t store in SQL Engine. When we execute this block it simply just compile in SQL Server, execute and produce output.

Example of Anonymous Block

DECLARE @i int;
SET @i=0;
BEGIN
      WHILE (@i<10)
      BEGIN
            SET @i=@i+1
            PRINT @i
      END
END

This code will compile and execute perfectly but not store in Database, so for execute the same logic we need to write this set of code every time.


2) Named Block: when we write a block of code with a Name then it is called Named Block. Once the named code executed it is compiled and stored into the database. As the block of code has name so we may implement the logic of this block every time by just calling its name.
If we were to name a block of code we may end up writing a

Procedure
Trigger
Function


1 comment:

  1. Very Usefull article,
    but how i execute this Anonymous block From a Dataset client ? from visual basic or c# ?
    thanks in advance

    ReplyDelete