Isolation Levels in SQL Server – SQL Server Tutorial

Isolation Levels in SQL Server :- Locks are controlled by isolation levels, Types of isolation levels: Read uncommitted Read committed Serializable (I) Read uncommitted in SQL Server  :- If the isolation level is set to read uncommitted then SQL Server doesn’t place any locks. (II) Read committed in SQL Server  :- If the isolation level is set to read  committed then shared locks are released immediately after …

Locking in SQL Server – SQL Server Tutorial

Locking in SQL Server :- Locking resolves of concurrent access where concurrent access means accessing same data by the number of users at same time. The following problem occurs concurrent  access: dirty read last update Locks types are Two types: Shared lock ->   It is denoted by (‘s’) exclusive lock ->  it is denoted by ‘x’ Shared lock …

CLR Integration in SQL Server – SQL Server Tutorial

CLR Integration in SQL Server :- The stored procedures & functions can also be created by using c# (or) VB. And the objects are called “CLR database objects” The difference is tsql is “procedure oriented”and c#,VB is “object oriented”. To create CLR database objects to follow the steps: create assembly in c# on VB. create …

DDL Triggers in SQL Server – SQL Server Tutorial

DDL Triggers in SQL Server :- It is introduced from sql server 2005. This triggers are created to control DDL operations like create,alter,drop. Syntax :–  create trigger <name>                         On database                     After create ,alter,drop                   As                  Begin Statements                End Example  :-   Create trigger trg10                         On database                          After create create_table                           As                            …

DML Triggers in SQL Server – SQL Server Tutorial

DML Triggers in SQL Server  :- These triggers are created on particular table. These triggers are created to control DML operators. Syntax  :- Create /alter trigger <name> Or<tablename>/<viewname>   After/instead of insert,update,delete →triggers event   As  Begin Statements End. After triggers in SQL Server :- These triggers are fired (executed) after the DML operation is executed. INSTEAD …

Table Valued Functions in SQL Server – SQL Server Tutorial

Table Valued Functions in SQL Server  :- These functions returns set of records  i:e table. The return type of this functions must be “table”. The return expression is  ”select statement”. Syntax :-                Create/alter function <name> (parameters)                   Returns table                    As              Return(select statement). Example  :-            Create function getemp(@d  int)            Returns table …

Function Types in SQL Server – SQL Server Tutorial

Function Types in SQL Server   :- It can be divided into two types Scalar valued Table valued Scalar Valued function in SQL Server:- These functions returns only one value of type int,varchar date time etc. Syntax  :–                  Create/alter function <name>(parameters)                      Returns <type>                           As                        Begin                        Statements                     Return<exper>                          End. Example …