#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.

 

#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.

#65 Clustered Vs Non-Clustered Index

Need: Data/Records are Stored in heap,they are generally put up in the next available space over heap without any arrangement(Sorting).This kind of strategy is good when every time data is inserted,but when a selection over data is made on heap,we would encounter performance barrier and our selection query would be too slow because of our arrangement of Data over heap. Indexing provides a solution by defining an arrangement of records over heap.

Types:

1.Clustered Index :Only one Per Table and Uses B-Tree where each leaf node points to data in heap.They are sorted and B-Tree Structure Changes every time when an insertion is made.

2.Non-Clustered Index(NCI):There can be one or more NCI per Table and their leaf node does not point to data but instead point to address/Location.