SQL Server View: Views are basically virtual table whose column and rows are defined based on query results. Its more over selection of columns from one or more table.
view can be used for the following purposes:
- To focus, simplify, and customize the perception each user has of the database.
- As a security mechanism by allowing users to access data through the view, without granting the user’s permissions to directly access the underlying base tables.
- To provide a backward compatible interface to emulate a table whose schema has changed.
-- Syntax for SQL Server Views
CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
[ WITH CHECK OPTION ]
[ ; ]
//------Below is the list of View attribute
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ]
--TSQL Example of a View
Create View dbo.VW_Enzo
Select FirstName,LastName,Email from Person
- SELECT statement must be a single table statement or a multi table JOIN with optional aggregation. A SELECT statement can not have following
- An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement
- INTO keyword
- OPTION Clause
- A reference to a temporary (#table) table or a table variable (@tablevariable).
- SCHEMABINDING: It’s bind the schema of table/ tables to the view. You can not modify or drop the table /tables, views that is used in view. To make any changes on the table you must alter/drop the view first or remove schemabinding from view.
- ENCRYPTION :Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication. You can not view the script of View using Sp_helptext.
- VIEW_METADATA : Specify the Metadata information of SQL Server instance like DB-Library, ODBC, and OLE DB APIs.
Important Points about View:
- View can be created only in current database.
- View can have maximum 1024 columns.
- When querying on view, it will check whether all the reference object exist then only it will execute, otherwise it will give error.
- sp_refreshview should be run when changes are made to the object’s underlying the view that affect the definition of the view only if the view is not having with SCHEMABINDING.
- When View is created information about view is stored in sys.views, sys.columns, and sys.sql_expression_dependencies.
- Database Engine saves the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a view is created.
- You cannot create a trigger or an index on a view.
- You cannot use the UNION operator within a CREATE VIEW statement. You can use the UNION operator to merge two views into a single result set.
- Modification statements (INSERT, UPDATE, or DELETE) are allowed on multi table views if the statement affects only one base table at a time.
- You cannot use READTEXT or WRITETEXT on text or image columns in views. You must access the underlying table or tables to use these commands.
- You cannot create a view on temporary tables.
- Partitioned Data: Showing user restricted data row or column. There are two types of partitions.
- Horizontal partition: limiting the column in data or showing fewer columns.
- Vertical Partition: limiting rows by putting where Clause. Restricting rows.
- Multiple table data: You can combine data from more than one table into a single view that from the end user’s perspective appears to be a single table.
- Computed Values : we can have calculation on base table data and show it as column in view.