Veiw and its Advantages in
Sql Server and What is INDEX VIEWS what is solution of this error SQL SCHEMABINDING Error
View:-- A view is nothing more than
a saved SQL query. A view can also be considered as a virtual table. A view is defined by a query and
only shows the data which is defined at the time of creation of the view. If
you hide particular columns then use the view. A view hides the complexity of
the underlying database schema, or customizes the data and schema for a set of
users.
Advantages of view:-
- View can be used to reduce the complexity of the database schema
- Views can be used as a machinsm to implement row and column level security.
- View can be used to present aggregated data and hide detailed data.
- A view is virtual, the data from a view is not stored physically. It is a set of queries that, when applied to one or more tables, is stored in the database as an object
- A view encapsulates the name of the table. A virtual table contains column and data from multiple tables
- Views are used for security purpose in the database because it hides the name of the table and logic and provides the virtual table. A view is used to store the SELECT statement only
- In the view we can modify the data and table. A view provides a convenient way to retrieve and manipulate data. A view contains rows and columns, the same as a real table. The fields in a view are fields from one or more real tables in the database. A view shows up-to-date
create table
Department
(
DeptId int primary key,
Deptname
varchar(23)
)
create table Employee
(
Id int,
Name varchar(32),
Salary float,
Gender varchar(23),
DepartmentId
int foreign key references Department(DeptId) on delete cascade
)
insert into Department
values(1,'IT')
insert into Department
values(2,'HR')
insert into Department
values(3,'PAYROLL')
insert into Department
values(4,'ADMIN')
insert into Employee values(1,'Kiran',4500,'male',3)
insert into Employee values(2,'Sara',3400,'female',1)
insert into Employee values(3,'john',4000,'male',2)
insert into Employee values(4,'kenny',5200,'female',4)
SELECT * FROM Department
SELECT * FROM Employee
---now join the tables
select id,Name,Salary,Gender,DepartmentId
from Employee
join Department
on Employee.DepartmentId=Department.DeptId
---create the view…………………
create view
VWGetbyDepartment
as
select e1.id,e1.Name,e1.Salary,e1.Gender,d1.Deptname
from Employee e1
join Department d1
on e1.DepartmentId=d1.DeptId
--use store procedure query like
this
sp_helptext
VWGetbyDepartment
select * from VWGetbyDepartment
---we used join in creating view the
table procedure only IT data from table----
create view
VWDepartment_IT
as
select e1.id,e1.Name,e1.Salary,e1.Gender,d1.Deptname
from Employee e1
join Department d1
on e1.DepartmentId=d1.DeptId
where d1.Deptname='IT'
-- query---
select * from VWDepartment_IT
---we can view for summarized data from table----
create view
VWSummarize_Data
as
select d1.Deptname,Count(e1.id) as 'Total Employee'
from Employee e1
join Department d1
on e1.DepartmentId=d1.DeptId
group by
d1.Deptname
-- query---
select * from VWSummarize_Data
To Modify a view –ALTER
VIEW statement.
To Drop a view- DROP VIEW
VWname.
UPDATEABLE VIEWS :-
SELECT * FROM
dbo.Department
SELECT * FROM
dbo.Employee
create view VWexceptsalaryEmployee
as
select id,name,gender,departmentId
from Employee
---here this create view select data from table Employee
then execute that
select * from
VWexceptsalaryEmployee
----now we update a view in table that
update VWexceptsalaryEmployee
set
Name='sapna' where id=4
--after executed data updated-then select query
used that---
----now we can delete data increated view----
delete from VWexceptsalaryEmployee where id=3
---after execute the view data deleted
----insert data in created view----
insert into VWexceptsalaryEmployee values(3,'john','male',2)
---after execute data insert---
---update view in both tables using create view----
create view
VWbothviewtabls
as
select id,name,gender,deptname
from dbo.Employee
join dbo.Department
on Employee.DepartmentId=Department.DeptId
---then select data---
select * from VWbothviewtabls
---update the created view in both table
update VWbothviewtabls
set Name='kenny' where Deptname='IT'
---after execute this query data is update
Conclusion:- if a view is based on multiple tables, and if
you update the view, it may not update the underlying base tables correctly. To
correctly update a view, that is based on multiple table, INSTEAD OF triggers
are used.
We will discuss about triggers and correctly updating a view
that is based on multiple tables.next
VIEW LIMITATIONS:-
- You can not pass parameter to a view. Table valued functions are not excellent replacement for parameterized views.
- Rules and default cannot be associated with views.
- The ORDER BY clause is invalid in view unless TOP or FOR XML is also specified.
- View can not be based on temporary tables.
---create table in another selected----
select * from
dbo.Employee
---now create the view--
create view VWviewlimitations
as
select Id,name,salary,gender,departmentId
from dbo.Employee
---after created
select * from
dbo.Employee
alter view VWviewlimitations
as
select Id,name,salary,gender,departmentId
from dbo.Employee
order by id
--In view created order by id it occured error like
this-:
--ERROR
--Msg 1033, Level 15, State 1, Procedure VWviewlimitations, Line 5
--The ORDER BY clause is invalid in views, inline
functions, derived tables, subqueries, and common table expressions, unless TOP
or FOR XML is also specified.
select * from
VWviewlimitations
---with parameter Error : cannot pass parameter
view as same
create view VWviewlimitations
@gender nvarchar(32)
as
select Id,name,salary,gender,departmentId
from dbo.Employee
where Gender=@Gender
--it will occured error if we execute that
query----
---then now we create inline table value function
in view
select * from
VWviewlimitations where gender='male'
create function Fn_viewtbledetails(@gender
varchar(33))
returns table
as
return
(select Id,name,gender,departmentId
from Employee where gender=@gender)
select * from
Fn_viewtbledetails('male')
--we can not
create temporary table on temp tables---
create table ##Tempotable
(
Id int,
name varchar(32),
gender varchar(23)
)
insert into ##Tempotable values(101,'Tom','Male')
insert into ##Tempotable values(102,'sara','female')
insert into ##Tempotable values(103,'kenny','female')
insert into ##Tempotable values(104,'sam','Male')
select * from
##Tempotable
--if we create
again temporary table then.....
create view VWTemptable
as
select id,name,gender
from
##Tempotable
--it will occured the same error
--Msg 4508, Level 16, State 1, Procedure VWTemptable, Line 4
--Views or functions are not allowed on temporary
tables. Table names that begin with '#' denote temporary tables.
INDEX VIEWS:- A standard or Non-Indexed view, is just a stored SQL
query. When we try to retrieve data from the view, the data is actually
retrieved from the underlying base tables.
So, view is just a virtual table it does not store data by
default.
However, when you create the index , on a view, the view gets
materialized. This means, the view is now, capable of storing data.
In sql server, we call them indexed view and in oracle, materialized
view.
Guidelines
for creating Indexed View
- The view should be created with schema Binding option
- If an aggregate function in the select list references an expression, and if there is a possibility for that expression to be null, then a replacement value should be specified
- If group by is specified, the view select list must contains a Count_Big(*) expression
- The base tables in the view, should be references with two part name.
-- Sql
Query..............
use test
create table
tblProducts
(
ProductId
int primary key,
Name varchar(50),
UnitPrice
int
)
create table
tblProductSales
(
ProductId
int foreign key references
tblProducts(ProductId),
QuantitySold
int
)
insert into
tblProducts values(1,'Pens',56)
insert into
tblProductSales values(1,6)
--we can create view from
table----
create view
VMTotalSales_ByProduct
with schemaBinding
as
select Name,
Sum(isnull((QuantitySold * UnitPrice),0)) as TotalSales,
COUNT_BIG(*) as
TotalTransactions from tblProductSales join
tblProducts on
tblProducts.ProductId=tblProductSales.ProductId
group by
name
Error
Msg 4512, Level 16, State 3, Procedure VMTotalSales_ByProduct, Line
21
Cannot schema bind view 'VMTotalSales_ByProduct' because name
'tblProductSales' is invalid for schema binding? Names must be in two-part
format and an object cannot reference itself.
Solution:
When You Create a view "WITH
SCHEMABINDING", Database will keep the reference with object name. many
database object can be created with same name from difference user, if we
create the view by using only name. There are lot of difficulties to handle. To
avoid that, SQL Server expects the Object Name in Proper Format like
Owner.Object_Name.
In My Query I changed to DBO.tblProductSales , then i tried to create the view then
it works fine.
create view
VMTotalSales_ByProduct
with schemaBinding
as
select Name,
Sum(isnull((QuantitySold *
UnitPrice),0)) as TotalSales,
COUNT_BIG(*) as TotalTransactions from dbo.tblProductSales join dbo.tblProducts on
tblProducts.ProductId=tblProductSales.ProductId
group by name
-- create unique Clustered Index
which name UIxwithName
create unique Clustered Index
UIxwithName
on
VMTotalSales_ByProduct(Name)
select * from VMTotalSales_ByProduct
Result
0 comments:
Post a Comment