#Sql -Transactions Basic

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.

 

 

#Sql Basics – joins

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.

 

#Sql Basics -Triggers

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&nbsp;

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.

 

#Common Performance Traps in Entity Framework

I recently read an article from SimpleTalk. It pretty much covered the common mistake and i am listing the one which i commonly do.

Bring only Columns you require,instead of All :

Lets assume we need only first name and last name from the table User  which holds information of user details (First Name, Last Name, Telephone No,Email Id,Location).

we need to write a query to bring all users whose location is  NewYork.

Most Common Query we write:


string Location= "NewYork"

var result= dbcontext.User.where(x=>x.Location==Location).ToList();

foreach(var usersinnewyork in result)

{

Console.WriteLine(usersinnewyork.FirstName);

Console.WriteLine(usersinnewyork.LastName);

}

In the above query the result brings all the columns while we need only first and last name.We might not be aware of impact of query we write,but identifying such queries in application would help us to avoid certain performance issues.

Modified Query with Anonymous Object :


string Location= "NewYork"

var result= dbcontext.User.where(x=>x.Location ==Location).Select( x=>new 

{
FirstName =x.FirstName,
LastName =x.LastName
});

In the above query we only bring what we need (first and last name).

 

#Basics of Views-Sql Server

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&amp;nbsp;

as

select &amp;nbsp;v1.column1 ,v1.column2 from v1&amp;nbsp;

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.

#Sql Basics -Union vs Union All

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.