TranCount : Returns the number of active transaction in the current connection.

Behavior of TranCount:

1.Increments the Count by 1 for every transaction.

2.Decrements the count by 1 for every commit.

3.Sets the Count to “0” on Rollback.

 

Transactions : A transaction is a set of sql statements that are executed as single sql statement,If the statement executes successfully Commit the transaction else Rollbacks on failure.

Usage :

Begin tran
insert into dbo.sampletable values('',null,GETDATE(),null)
insert into dbo.sampletable values(1,'',null,GETDATE(),null) //Inserting identity value will fail the query.
IF @@ERROR<>0
ROLLBACK
ELSE
COMMIT

In the above code,i am making an insert into an identity column which will fail the query and in turn roll back the transaction and also make a note that none of the rows would be inserted since transaction failed.

 

 

Quick Basics of Join.

Types of Join :

1.Inner Join-Intersection of two tables.

2.Left Outer Join-Intersection of two tables + rows in left table.

3.Right Outer  Join-Intersection of two tables +rows in right table.

4. Outer Join-Left join +Right Join ,null values will be substituted when condition is not met.

5.Cross Join – A Cartesian product(No of rows in A * No of rows in B) between two tables,there is no “on” condition.Produces all combination of rows between two tables.

Syntax for cross join :

select * from tablea cross join tableb

(or )

select * from tablea,tableb

6.Self Join : Join among the same table.

 

Triggers :

A trigger is a special kind of stored procedures that automatically executes when an event occurs in database.You cannot invoke triggers explicitly.

Types of triggers :

1.Instead of Trigger

2.After Trigger(for Triggers)–Does not supports view.

Instead of Triggers :

As the name suggest,you take control over Insert/Update/Delete and you decide if you really want to Delete a table record that has to be deleted even though you give a delete statement over table.

create trigger trigeer_name on your_table 

instead of delete 

As

declare @id int;
Begin
select id=d.Id from deleted d;

/**your operation**/

End

After Trigger :

Events that has to occur after Insert/Update/Delete are termed as After Triggers.

Create trigger your_triggername on your_table;

for Insert;

as

begin

select id=d.Id from inserted (inserted is a special table provided by sql)

/**your operation **/

end

How to get column values after/before  statement(Insert/Update/Delete) gets executed in triggers ?

Sql provides special tables for triggers namely inserted,deleted which will hold the records/column values,we can make use of this table in after/instead of triggers.

 

Views : They are virtual table and are generally combination of 2 or 3 tables.Being said Virtual they do not occupy space like a normal table.

Indexes on View :

Views are virtual table by default,to use index it’s mandatory to have a physical column.To enable indexes its mandatory to use SCHEMABINDING with views.

create view yourviewname 

with schemabinding 

as

select  v1.column1 ,v1.column2 from v1 

Refreshing Views:

Since views are virtual tables,there might be cases where we need to refresh a view when underlying table used in view changes.

Execute SP_RefreshView "yourviewname"

 

Insert/Update/Delete with Views:

 Single select must be used in view with check option enabled.

Union – Merges the Content of two or more tables without duplicates.

Union All-Merges the Content of two or more tables which include duplicates.

For union/union all between two or more tables you must have same column name including the data type.

Performance : 

Union All does not requires elimination of duplicate records and the performance is high when compared with Union since it requires an additional operation to remove the duplicate values.