SQL is a standard Structured query language for storing, manipulating and retrieving data in databases. View
1) What is a View?
A view is a saved SQL query. A view can also be considered as a virtual table. actually retrieves the data from the underlying base tables. vtblEmployee, vtblDepartment
Create View vWName
Views can be used to reduce the complexity of the database schema, for non IT users. Views can be used as a mechanism to implement row and column level security. Row Level Security
where tblDepartment.DeptName = 'IT'
To look at view definition - sp_helptext vWName ; To modify a view - ALTER VIEW statement ; To Drop a view - DROP VIEW vWName.
2) Updateable Views
Is it possible to Insert, Update and delete rows, from the underlying tblEmployees table, using view vWEmployeesDataExceptSalary? Yes, SQL server views are updateable.
Set Name = 'Mikey' Where Id = 2
3) Indexed views
when you create an 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 views and in Oracle, Materialized views. vtblProduct , vtblProductSales
If you want to create an Index, on a view, the following rules should be followed by the view. For the complete list of all rules, please check MSDN.
1. The view should be created with SchemaBinding option
2. If an Aggregate function in the SELECT LIST, references an expression, and if there is a possibility for that expression to become NULL, then, a replacement value should be specified. In this example, we are using, ISNULL() function, to replace NULL values with ZERO.
3. If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression
4. The base tables in the view, should be referenced with 2 part name. In this example, tblProduct and tblProductSales are referenced using dbo.tblProduct and dbo.tblProductSales respectively.
1. You cannot pass parameters to a view. Table Valued functions are an excellent replacement for parameterized views.
2.Rules and Defaults cannot be associated with views.
3.The ORDER BY clause is invalid in views unless TOP or FOR XML is also specified.
4.Views cannot be based on temporary tables.