Category: 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 …

Continue reading “Isolation Levels 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 …

Continue reading “Locking 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 …

Continue reading “CLR Integration in SQL Server – SQL Server Tutorial”

XML Integration in SQL Server :- Xml is simple and Unicode And platform independent. Transfer data from one file to another For each record one tag is created with name “row” Select * from emp for xml row Select * from emp For xml path.  select * from emp for xml   xpath root(‘emp’) Transfering data from …

Continue reading “XML Integration 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                            …

Continue reading “DDL 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 …

Continue reading “DML Triggers in SQL Server – SQL Server Tutorial”

Database Triggers in SQL Server:- A database trigger is also a ‘named block’ like procedures executed implicitly. Procedures in SQL Server :                           Called explicitly                                Procedure can be created  without …

Continue reading “Database Triggers 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 …

Continue reading “Table Valued Functions 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 …

Continue reading “Function Types in SQL Server – SQL Server Tutorial”

Functions in SQL Server :- A function is also a named tsql block takes some input performs a task and must returns a value. Difference between Procedures and Functions in SQL Server: Procedures : A procedure need not return a value. Returns values using out parameters. procedure can return more than one value procedure can’t be …

Continue reading “Difference between Procedures and Functions in SQL Server Tutorial”