How to Find Blocking Queries in Transaction In SQL server?

In this article, we will learn Blocking Queries In Transaction and Except Operator and difference between Except and Not In Operator in SQL Server By Sagar Jaybhay.

Blocking Queries

Blocking Query happens because there is an open transaction.

DBCC OpenTran is a command for checking the open transaction but there is a problem it only shows the oldest active transaction. It is not going to show you an open transaction.

dbcc opentran

We write a transaction and execute that and run dbcc opentran command see below image

dbcc opentran 2

Below is a query which gives you all open transaction this query I found one of the blogs whose link is this.

https://stackoverflow.com/questions/4449719/is-there-a-way-to-list-open-transactions-on-sql-server-2000-database

SELECT
trans.session_id AS [SESSION ID],
ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
trans.transaction_id AS [TRANSACTION ID],
tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION 
BEGIN TIME],
tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL

Except Operator in SQL server:

The Except Operator returns unique rows from the left query which are not present as a result of the right query.

  1. It is introduced in SQL server 2005
  2. The number and order of columns needs to be same
  3. The data types need to be same
select * from Employee where EmpID between 1 and 20
except 
select * from Employee where EmpID between 10 and 100;

In the above query, we have 1 to 9 empid is not present in the second query so it only returns 1 to 9 empid result.

What is the difference between Except and Not In operator in SQL Server?

Except operator returns unique rows from left result set which are not present in right result set and Not In operator also does the same.

select * from Employee where EmpID between 1 and 20
except 
select * from Employee where EmpID between 10 and 100;

The above query is for except operator and below is a query for not in operator which does the same.

select * from Employee where (EmpID >=1 and EmpID <=20) and EmpID
not in(
select EmpID from Employee where EmpID between 10 and 100);

So what is the difference between them?

  1. Except filters duplicate and returns only distinct rows from the left query that not in the right query but Not in Operator not filter the duplicates.
  2. Except operator columns need to be same and also datatype but Not in operator works with a single column from outer query to a single column in Inner query.

Profile Link of GitLab Account is:- https://gitlab.com/Sagar_Jaybhay

T SQL Advanced Tutorial By Sagar Jaybhay 2020

In this article we will understand T SQL Advanced Tutorial means Transaction In SQL and Common Concurrency Problem and SQL server transaction Isolation level by Sagar Jaybhay

What is the Transaction?

A transaction is a group of commands that changed the data stored in a database. A transaction is treated as a single unit.

The transaction ensures that either all commands will succeed or none of them. Means anyone fails then all commands are rolled back and data that might change is reverted back to the original state. A transaction maintains the integrity of data in a database.

begin try
begin transaction
	update dbo.account set amount = amount-100 where id=1
	update dbo.account set amount=amount+100 where id=2
commit transaction
print 'transaction committed'
end try
begin catch
rollback transaction
print 'transaction rolled-back'
end catch
Concurrent Transaction

In the above example either both statements executed or none of them because it goes in catch block where we rolled-back transactions.

begin try
begin transaction
	update dbo.account set amount = amount-100 where id=1
	update dbo.account set amount=amount+100 where id='A'
commit transaction
print 'transaction commited'
end try
begin catch
rollback transaction
print 'tranaction rolledback'
end catch
Transaction-Fail-and-Roll-Back

Common Concurrency Problem

  1. Dirty reads
  2. Lost update
  3. Nonrepetable reads
  4. Phantom reads

SQL server transaction Isolation level

  1. Read Uncommitted
  2. Read committed
  3. Repeatable read
  4. Snapshot
  5. Serializable

How to overcome the concurrency issues?

One way to overcome this issue is to allow only one user at the time allowed for the transaction.

Dirty Read Concurrency Problem:

A dirty read happens when one transaction permitted to read data that modified by another transaction but that yet not committed. Most of the time it will not cause any problem because if any case transaction fails then the first transaction rolled back its data and the second transaction not have dirty data that also not exist anymore.

To do 2 transactions on one machine open 2 query editor that is your 2 transaction machine and you do an operation like below

Multiple-Transaction

For the first transaction, we update the amount in the account table and then given a delay for 1 min 30 seconds and after this, we rollback the transaction. And in the second window, we select data from a table where we can see uncommitted data and after transaction rollback, we see committed data.

We have default isolation level read committed to set different for reading uncommitted data you can use below command.

set transaction isolation level read uncommitted;


-- the First transaction

begin transaction

update account set amount=amount+1000000 where id=1;

waitfor delay '00:01:30'
rollback transaction

-- Second Transaction

set transaction isolation level read uncommitted;
select * from account;
Success-Ful-Multiple-Transaction.png

Lost Update

It means that 2 transactions read and update the same data. When one transaction silently overrides the data of another transaction modified this is called a lost update.

Both read committed and read uncommitted have lost update side effects.

Repeatable reads, snapshots, and serialization do not have these side effects.

Repeatable read has an additional locking mechanism that Is applied on a row that read by current transactions and prevents them from updated or deleted from another transaction.

-- first transaction

begin transaction
declare @amt float
select @amt=amount from account where id =1;

waitfor delay '00:01:20'
set @amt=@amt-1000
update account set amount=@amt where id=1;
print @amt
commit transaction
-- first tarnsaction
-- second transaction
begin transaction
declare @amt float
select @amt=amount from account where id =1;

waitfor delay '00:00:20'
set @amt=@amt-2000
update account set amount=@amt where id=1;
print @amt
commit transaction
Lost Update

Non-Repeatable read

It was when the first transaction reads the data twice and the second transaction updates the data in between the first and second transactions.

Phantom read

It happens when one transaction executes a query twice and it gets a different number of rows in the result set each time. This happens when a second transaction inserts a new record that matches where the clause of executed by the first query.

To fix phantom read problem we can use serializable and snapshot isolation levels. When we use the serializable isolation level it would apply the range lock. Means whatever range you have given in first transaction lock is applied to that range by doing so second transaction not able to insert data between this range.

Snapshot isolation level

Like a serializable isolation level snapshot also does not have any concurrency side effects.

What is the difference between serializable and Snapshot isolation level?

Serialization isolation level acquires it means during the transaction resources in our case tables acquires a lock for that current transaction. So acquiring the lock it reduces concurrency reduction.

Snapshot doesn’t acquire a lock it maintains versioning in TempDB. Since snapshot does not acquire lock resources it significantly increases the number of concurrent transactions while providing the same level of data consistency as serializable isolation does.

See below the image in that we use a serializable isolation level that acquires a lock so that we are able to see the execution of a query in progress.

Snap Shot Isolation Level

Now in the below example, we set a database for allowing snapshot isolation. For that, we need to execute the below command.

alter database temp
set allow_snapshot_isolation on

Doing so our database tempdb is allowed for snapshot transaction than on one window we use serialization isolation level and on the second we use snapshot isolation level. When we run both transactions we are able to see the snapshot isolation level transaction completed while serialization is in progress and after completing both transactions we see one window has updated data and others will have previous data. First

Isolatio level

Now after completing both transactions

  1. snapshot isolation never blocks the transaction.
  2. It will display that data which is before another transaction processing
  3. It means that snapshot isolation never locks resources and other transaction able read the data
  4. But here one transaction is updating the data another is reading that data so it’s ok
  5. When both transactions updating same data then transaction blocks and this blocks until the first transaction complete and then transaction 2 throws error lost update why because preventing overwriting the data and it fails and error is transaction is aborted you can’t use snapshot isolation level update, delete insert that had been deleted or modified by another transaction.
  6. If you want to complete the second transaction you need to rerun that transaction and data is modified successfully.

Read Committed Snapshot Isolation Level

It is not a different isolation level. It is an only different way of implementing Read committed isolation level. one problem in that if anyone transaction is updating the record while reading the same data by another transaction is blocked.

Difference between Snapshot isolation level and Read Committed Snapshot isolation level.

Snapshot IsolationRead Committed Snapshot isolation level
It is vulnerable to update conflictsNo update conflicts here
Can not use with a distributed transactionIt can work with a distributed transaction
Provides transaction-level read consistencyIt provides statement-level read consistency

My Other Site: https://sagarjaybhay.net

Cursor In RDBMS By Sagar Jaybhay 2020

In this article we will understand cursor in rdbms in our case we show example on SQL Server By Sagar Jaybhay. Also we will understand Merge statement in SQL Server and rerunnable SQL scripts and How to create a stored procedure with an optional parameter?

Cursors In RDBMS

In a relational database management system takes into consideration then it would process the data in sets inefficient manner.

But when you have a need to process the data row by row basis then the cursor is the choice. The cursor is very bad at performance and it should be avoided and also you can replace the cursor with join.

Different Types of Cursors In RDBMS

Their are four types of cursors in rdbms which are listed below

  1. Forward only
  2. Static
  3. Keyset
  4. Dynamic

The cursor is loop through each record one by one so that’s why it’s performance is not good.

declare @empid int
declare @deptid int
declare @fullname varchar(200)

declare empcurose cursor for 
select EmpID,full_name,DepartmentID from Employee

open empcurose

fetch next from empcurose into @empid,@fullname,@deptid

while(@@FETCH_STATUS=0)
begin

print 'EmpID '+cast(@empid as varchar(10))+ ' Name '+cast(@fullname as varchar(100)) + ' deptid '+cast(@deptid as varchar(100))
fetch next from empcurose into @empid,@fullname,@deptid
end
close empcurose
deallocate empcurose
cursor in rdbms
deallocate empcurose

This line is used to deallocate all resources which are allocated for that cursor.

What is rerunnable SQL scripts?

A re-runnable SQL script is a script that runs multiple times on the machine will not throw any kind of error.

For example, if you use create table statement to create a table then use if not exist in create a statement so it will not throw an error.

How to create a stored procedure with an optional parameter?

create procedure searchemployee
@name varchar(10)=null,
@deptid int=null,
@gender varchar(10)=null
as
begin

if(@name is not null)
print 'i am in name '+cast(@name as varchar(20))
select * from tblEmp where [name]=@name;
return;

if(@deptid is not null)
print 'i am in deptid '+cast(@deptid as varchar(20))
select * from tblEmp where deptid=@deptid;
return;

if(@gender is not null)
print 'i am in gender '+cast(@gender as varchar(20))
select * from tblEmp where geneder=@gender;
return;
print 'i m here '+cast(@gender as varchar(20))+' '+cast(@deptid as varchar(20)) +' '+cast(@name as varchar(20))
select * from tblEmp

end

execute searchemployee @deptid=2

Simply pass default values to stored procedure variables.

Merge statement In SQL server

Merge statement is introduced in SQL server 2008 it allows to insert, update, deletes in one statement. It means there is no need to use multiple statements for insert update and delete.

In this, if you want to use merge statement you need to 2 tables

  1. Source table– it contains the changes that need to apply to the target table.
  2. Target table– this is the table that requires changes insert, update, delete.

Merge statement joins the target table to source table by using a common column in both tables based on how you match up we perform insert, update and delete.


Transaction Link: https://www.codementor.io/@sagarjaybhay18091988/transaction-in-sql-server-155l4qr7f4


Subqueries & Correlated Subqueries SQL Server 2020

In this article Sagar Jaybhay explain how to write Subqueries and Correlated Subqueries in SQL Server and What to choose for Performance Subquery or Join?

Subqueries In SQL Server:

Subqueries are enclosed in parenthesis. Subquery also called an inner query and the query which enclosed that inner query is called an outer query. Many times subqueries can be replaced with joins.

select * from Employee where DepartmentID not in (select distinct DepartmentID  from Department)

Another example

select Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID) from Department as d;

The above query is an example of use subquery in the select list. The above result can be achieved using join also see below query

select d.Department_Name,COUNT(e.empid) as empcount from Department d
join Employee e on e.DepartmentID=d.DepartmentID
group by d.Department_Name
order by empcount;

According to MSDN, you can nested up to 32 levels.

Columns present in subqueries can not be used in the outer select list of a query.

Correlated Subqueries:

If our subquery depends on the outer query for its value then it is called Correlated subqueries. It means subquery depends on outer subquery/ Correlated subqueries are executed for every single row executed by outer subqueries.

A correlated subquery can be executed independently

select distinct Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID group by DepartmentID) as empcount from Department as d  order by empcount;

What to choose for Performance Subquery or Join?

According to MSDN, there is no big difference between queries that use sub-queries and joins.

But in some cases, we need to check the performance and Join produces better performance because the nested query is must be processed for each result of the outer query. In such cases, JOIN will perform better.

In general, JOIN works faster as compared to subqueries but in reality, it will depend on the execution plan generated by SQL Server. If the SQL server generates the same execution plan then you will get the same result.

Transaction Link: https://www.codementor.io/@sagarjaybhay18091988/transaction-in-sql-server-155l4qr7f4

How to handle errors in SQL Server By Sagar Jaybhay

In this article we will understand How to handle errors in SQL Server By Sagar Jaybhay.

Handle Error

In SQL Server 2005 they introduced try/catch block in SQL server likes C# and Java.

In SQL Server 2000 they have syntax — @@Error

In SQL Server 2005 they have introduced – try/catch.

In SQL server the variables which are starting with @@ symbols are called global variables but hey are not variables but working like variables but they are similar to a function.

Throw an error in SQL Server we have a function, Raiserror(error_message, severity level, state)

In this Raiserror function, the first parameter is error_message which we want to display. Like throw keyword in C#.

The second parameter is Error Severity level– which is most cases is 16 means the user can resolve this error.

The third Parameter is State: It is a number between 1 to 255 but Raiserror will generate state between 1 to 127.

@@ERROR is a system function that contains non zero value if there is no error else it has 0 value.

@@ERROR is cleared and reset on each statement of execution.

Try/ Catch:

Whatever we can do with @@ERROR we can achieve it by using a try-catch block. You can write any number of statement inside the try block and if any error occurred then control directly moves in the catch block and the rest of the statement in try blocks are the skip. If no error will occur then the control bypass/skip the execution of the catch block.

Errors that are trapped in the catch block are not returned to calling function for that you need to use Raiserror function.

You can use system function which gives more information about the error and this can be called inside catch block only.

To write code in try-catch block use below syntax here

Begin Try
//-- Your code is here
End Try
Begin Catch
//-- Your code is here

End Catch

In SQL server to get more information SQL server provides functions for that which is described below

  1. Error_Number() : display how many errors occurred
  2. Error_message() : It returns the message of error    
  3. Error_Procedure(): returns the name of the stored procedure or trigger where an error occurs
  4. Error_State(): returns the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block
  5. Error_Severity(): returns the error severity value of an error, regardless of how many times it runs or where it runs within the scope of the CATCH block
  6. Error_Line (): returns the line number at which the error occurred. 

Above all of these functions are run inside the context of the catch block. Outside the catch block, it will return null.

GitHub: https://github.com/Sagar-Jaybhay

CTE (common table expression) In Depth

CTE (common table expression) and Updatable CTE you will understand this in this article by sagar jaybhay in depth.

CTE (common table expression)

CTE Introduced in the SQL server 2005.

CTE is like a temporary result set which is defined within the execution of the current context or execution scope of single select, insert, update delete and create view statement.

It is similar to a derived table and it is not stored as an object like other objects in the SQL server.

Remember CTE table is created with the keyword.

with CTEtable
as
(
select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d
join Employee as e on d.DepartmentID=e.DepartmentID
group by d.Department_Name
)
select * from CTEtable
where 
empcount>100;

CTE
CTE

In the above query, we didn’t mentioned the column name if your inner query is given distinct column name then there is no need to define column name else you need to define like shown below

with CTEtable(deptname,empcount)
as
(
select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d
join Employee as e on d.DepartmentID=e.DepartmentID
group by d.Department_Name
)
select * from CTEtable
where 
empcount>100;
CTE with column name defined
CTE with column name defined

In the above query, you specify 2 columns so remember you need to specify the columns that select query is returning if our inner select query returning 3 columns then you need to specify these 3 columns in CTE.

CTE is only referenced by select, insert, update and delete statement immediately follows the CTE expression.

In this, With clause, you can create multiple CTE tables.

with CTEtable(deptname,empcount)
as
(
select d.deptname as deptname, COUNT(e.id) as empcount from tbldept as d
join tblEmp as e on d.deptid=e.deptid
group by d.deptname
),
tblnew_hr(deptname,id)
as
(
select d.deptname,e.id from tblEmp e join tbldept d on
e.deptid=d.deptid
)
select * from CTEtable
union 
select * from tblnew_hr

Multiple CTE
Multiple CTE

Updatable CTE

It is possible to update the CTE the answer to this is Yes or No.

If your CTE is based on a single table then you can update using CTE. Which in turn update the underlying table.

with update_cte
as
(
select id, name, salary from tblEmp
)

update update_cte set salary=5555 where id =2

select * from tblEmp;
Updatable CTE
Updatable CTE

If CTE is based on more than one table and updates affect only the base table then this is possible.

with update_mul_cte
as
(
select e.id,d.deptname,e.geneder from tblEmp e join tbldept d on e.deptid=d.deptid
)

update update_mul_cte set geneder='male' where id=2;
select * from tblEmp;
Multiple CTE Update
Multiple CTE Update

But if you are going to update data in both tables which are present in CTE it will throw an error.

with update_mul_cte
as
(
select e.id,d.deptname,e.geneder from tblEmp e join tbldept d on e.deptid=d.deptid
)

update update_mul_cte set geneder='male',deptname='fff' where id=2;
select * from tblEmp;
Update Multiple Table CTE Error

Below is an error that is thrown by it.

Msg 4405, Level 16, State 1, Line 11

View or function ‘update_mul_cte’ is not updatable because the modification affects multiple base tables.


GitHub Profile :- https://github.com/Sagar-Jaybhay


What Is Triggers In SQL By Sagar Jaybhay Part 1

In this series of article you will understand What is Triggers in SQL Server and How many Different Types of Triggers Present in SQL Server by Sagar Jaybhay.

Triggers in SQL Server

In SQL server there are different types of triggers are present

  1. DML triggers
  2. DDL triggers
  3. Logon triggers

DML Triggers

This type of trigger is fired automatically when DML query is executed on an underlying table means Insert, Update or Delete query fired then DML triggers are triggered.

DML stands for data manipulation trigger and it is fire when data is modified using this insert, update or delete query or command.

DML triggers are classified into 2 different types of triggers

  1. After triggers / For triggers
  2. Instead of triggers

After Trigger

The name itself suggests that when the query is executed after that after trigger will fire. This means that after triggering action the after trigger will fire. It means after complete execution of Insert, Update and Delete query the trigger will fire.

Instead Of Trigger

This instead of trigger will fire, instead of triggering action. This Insert, update and delete are causes to fire instead of trigger.

In general, we can consider a trigger a stored procedure or function which can trigger after some kind of triggering action.

When you create a trigger you can create this for a specific table and specific event.

After Trigger

This trigger will fire after the operation is completed means insert, update or delete.

From this trigger, we get inserted row in which whatever the value we inserted table we get this. Inserted is a table which is also called a Magic table which is maintained by the SQL server and which retains a copy of row which we inserted into the table. It is accessed inside the context of creating a trigger

We are having simple student table in that 5 rows and we are creating after triggering on insert by which trigger will fire when we insert a row and whatever value inserted we get this simply by using select * from inserted; query in the trigger. Below is a query for that trigger

create trigger afterinsert
on studenttable
for insert
as
begin
select * from inserted
end;

AfterInsert Trigger
AfterInsert Trigger

In this above image, you can see when insert command completed successfully the select will fire.

Now we have to add newly added row into another table how we do that

alter trigger afterinsert
on studenttable
for insert
as
begin
declare @id int;
declare @fullname nvarchar(2000);
select @id=Id from inserted;
select @fullname= first_name from inserted;

insert into afterinserttable values(@id,'name is '+CAST( @fullname as nvarchar(100)) + cast(GETDATE() as nvarchar));
select * from afterinserttable;
end;

insert into studentTable values(7,'sagar1','jaybhay1','sagar1@sagarjaybhay.net','Male','2020-02-07');

In the above query, we inserted data into the newly created table which is afterinserttable and we select all rows from that table. When the trigger is called row is inserted and all rows from that table are selected. See below image

Alter afterinsert trigger
Alter afterinsert trigger

In insert trigger, we get Inserted table in a trigger like that for delete trigger we get deleted table in that create a table. If we try to access this outside create trigger syntax we get an error.

create trigger afterdelted
on studenttable
for delete
as
begin
declare @id int;
declare @fullname nvarchar(2000);
select @id=Id from deleted;
select @fullname= first_name from deleted;

insert into afterinserttable values(@id,'name is '+CAST( @fullname as nvarchar(100)) + cast(GETDATE() as nvarchar));
select * from afterinserttable;
end;

GitHub: https://github.com/Sagar-Jaybhay

What Is Indexed View In SQL By Sagar Jaybhay

In this article we will understand What Is Indexed View In SQL Server By Sagar Jaybhay.

Indexed View

A normal view or non-indexed view is a saved SQL query or a virtual table. When you try to get data from the table the data is coming from an underlying table. So it means the view is a virtual table and doesn’t store any data.

But when you create an index on view it gets materialized which means you can store data in view so in the SQL server we called is as Indexed view.

There are some guidelines for creating an indexed view.

  1. The view should be created with schemabinding option.
  2. If in your select function contains aggregate functions like sum, avg like that then for this you need to replace the null value with other value means 0 or not null value.
  3. If your select query contains group by clause then you need to use Count_Big(*) expression.
  4. Whatever table you used in view you need to specify 2 part names of these tables the example of above shown below

create view IndexedView
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from Employee as e 
inner join Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

Indexed View In SQL By Sagar Jaybhay
Indexed View In SQL By Sagar Jaybhay

Now by using the above query, we created a view but we are not able to create an index on that so that you can get below error

Msg 1939, Level 16, State 1, Line 59

Cannot create an index on view ‘IndexedView’ because the view is not schema bound.

Indexed View In SQL Error Message By Sagar Jaybhay
Indexed View In SQL Error Message By Sagar Jaybhay

For this, we need to create a view schemabinding option and now we can alter our view. For altering view our query looks like below

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

Msg 4512, Level 16, State 3, Procedure IndexedView, Line 4 [Batch Start Line 58]

Cannot schema bind view ‘IndexedView’ because the name ‘Employee’ is invalid for schema binding. Names must be in two-part format and an object cannot reference

Now when we are altering the view with schema binding option we get the above error. To fix this we need two-part names of the table.

Now our view gets created by using below query

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from dbo.Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

But when we are going to create the index we will get below error

Msg 10138, Level 16, State 1, Line 68

Cannot create an index on view ‘temp.dbo.IndexedView’ because its select list does not include proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select a list.

Now we add count_big(*) in our query

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(isnull(e.salary,0)) as totalsal,
COUNT_BIG(*) as totalemp
from dbo.Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

and when we use below query

create unique clustered index view_deptindex
on IndexedView(dept);

to create an index it works perfectly fine.

Updatable View in SQL By Sagar Jaybhay

In this article we will understand how to Update view or What it means In SQL Server By Sagar Jaybhay.

Before Proceeding To this article Please Read Part 1 – https://sagarjaybhay.com/view-in-sql-by-sagar-jaybhay-2020/

Updatable View in SQL Server

By using view it is possible to update the base table.

By using view it is possible to update the base table.
By using view it is possible to update the base table.

Updatable View 2
Updatable View 2

Query for this

update empwithdepartment set full_name='flower blossam' where EmpID=2;

Updatable View 3
Updatable View 3

See the above image you will see the result the data is updated but it not present in view. It is updated in the underlying table so If you want to find this whether it is updated or not fire below query.

select top 4 * from empwithdepartment;

Updatable View 4
Updatable View 4

In the above image, you will find the base table also gets updated.

Like the update, you can also delete data from view or insert data into view which turn deleted or inserted in the underlying table.

Updatable View 5
Updatable View 5

But in this case, we got an error why.

Msg 4405, Level 16, State 1, Line 35

View or function ’empwithdepartment’ is not updatable because the modification affects multiple base tables.

Because our view is based on multiple tables so we are not able to insert records in view or it might update records incorrectly but if our view is based on a single table then we are able to add records in view which in turn added in a base table.

When you trying to insert data into view and view finds that multiple tables contain these fields so it gets confused and throws an error.

Updatable View 6
Updatable View 6

In the above figure, you will understand we create a view that is based on a single table and we are able to insert records using the view.

create view simpleview
as
select EmpID,full_name,Salary,Gender from Employee where Gender='Male';

insert into simpleview values(1002,'Sagara',20000,'Male');

select * from simpleview where EmpID>1000;
select * from Employee where EmpID=1002;

View in SQL By Sagar Jaybhay 2020

In this article Sagar Jaybhay explain what is View. What is the use of View. what is the advantages of views in SQL server.

View

The view is saved SQL query or we can call it a virtual table.

View In SQL 1
View In SQL 1

We have these 2 tables and by joining these 2 tables we want to output.

View In SQL 2
View In SQL 2

By joining these 2 tables we get the above result. Now we want to create a view, like other create statement like create a table, create procedure we have to create view statement

Below is a query for creating a view

create view empwithdepartment
as
select e.EmpID,e.full_name,e.Salary,e.Gender,d.Department_Name from Employee as e
join Department as d on d.DepartmentID=e.DepartmentID;

View In SQL 3
View In SQL 3

To find a view in the database refer below image

How to find view
How to find view

If you check the above query it just selects query and if you want to get data from the view you will able to treat it as a table and simple select * from view_name; by using this you can get data.

View doesn’t store any data and it is just saved select query.

Advantages of views

  1. It is used to reduce the complexity of database schema
  2. It provides a mechanism to implement column level and row-level security means if you want to give access to certain users with a limited number of rows and columns then put your query in view and given that view name to end-user so that he performs the operation on that considering this is a table. By doing this end-user doesn’t know the underlying base table.
  3. It Is used to present aggregated data or detailed data.

To alter view you can use alter view syntax

Alter view view_name
As
-	your query syntax here

To drop the view you can use

Drop view view_name;


GitHub : https://github.com/Sagar-Jaybhay