Introduction
In this tutorial, We will create an SQL view statement. The SQL View is a virtual table created from a query that retrieves data from one or more tables whose contents, columns, and rows are defined by a query. Views can simplify complex queries, provide security by limiting access to sensitive data, and present data in a customized format.
You can use this statement to create a view of the data in one or more tables in the database. A view can be used for the following purposes.
- It focuses, simplifies, and customizes users' perceptions of the database.
- It can be used as a security mechanism by allowing users to access data through the View without granting the users permission to access the underlying base tables in the view statement directly.
- It can also provide a backward-compatible interface to emulate a table whose schema has changed.
Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Important
In the columns for the view statement, the permissions for a column name apply across a Create view or alter view statement, regardless of the source of the underlying data in the view statement. For example, suppose permissions are granted on the OrderId column in a CreateView statement. In that case, an Alter view statement can name the OrderId column with different column names, such as OrderRef, and still have the permissions associated with the View using OrderId. Any updates performed directly to a view's underlying tables are not verified against the View, even if the Check option is specified in the view statement.
Statement 1. Schema binding in view statement
The view statement binds the View to the schema of the underlying table or tables when Schema binding is specified; the base table or tables cannot be modified in a way that would affect the view definition; the definition itself must first be modified or dropped to remove dependencies on the table that are to be modified in View. When you use Schemabinding, the select_statement must include the two-part names schema. The object of tables, views, or user-defined functions that are referenced in View all referenced objects must be in the same database in the view statement.
The views or tables that participate in a view created with the Schemabinding clause cannot be dropped unless that View is dropped or changed so that it no longer has schema binding. Otherwise, the database also raises an error, executing Alter Table statements on tables that participate in the visit have schema binding fail when these statements affect the view definition in the view statement.
When creating a view, you can use the Schema binding option to specify that the View should be bound to the schema of the underlying tables or views. This can be useful in several ways.
- Performance- Schema binding can improve performance because SQL Server can optimize the query execution plan based on the knowledge that the underlying schema will not change.
- Security- Schema binding can also be used to ensure that users cannot modify the underlying objects in a way that would cause the View to fail.
- Dependencies- Schema binding can create a dependency between a view and the underlying tables or views, making it easier to manage changes to the schema.
Note. that once a view is schema-bound, you cannot modify the underlying tables or views without first dropping the View. Therefore, you should consider whether schema binding is appropriate for your situation before using it.
Statement 2. View_MetaData statement
The View _metadata statement specifies that the instance of SQL Server will return the Db-library, Odbc, and Ole Db Apis the metadata information about the View instead of the base table or tables when browse-mode metadata is being requested for a query that references the View. Browse-mode metadata is additional metadata that the instance of SQL Server returns to these client-side Apis.
This View_ metadata enables the client-side Apis to implement updatable client-side cursors. Browse-mode metadata includes information about the base table that the results set's columns belong to the view statement; for views created with View_metadata, the browse-mode metadata returns the view name and not the base table names when it describes columns from the View in the result set. When a view is created using With View_MetaData, all its columns, except a timestamp column, are updatable if the View has instead of Insert or Update triggers in the View statement.
You can use the following query to retrieve metadata information about a view in SQL.
SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'schema_name' AND TABLE_NAME = 'view_name';
This query retrieves information about the View named 'view_name' in the schema named 'schema_name.' The result set contains columns such as 'VIEW_DEFINITION,' 'CHECK_OPTION,' and 'IS_UPDATABLE' that provide information about the View's definition and characteristics.
Note. that the exact syntax and available metadata may vary depending on the specific SQL database system used.
Statement 3. Updatable views statement
You can modify the data of an underlying base table through a view as long as the following conditions are true.
- Any modifications, including Update, Insert, and Delete statements, must reference columns from only one base table in View.
- The columns being modified in the View must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following statements.
- An aggregate function Avg, Count, Sum, Min, Max, Grouping, Stdev, Stdevp, Var, and Varp.
- The computation of the column cannot be computed from an expression that uses other columns. Columns formed using the set operators Union, UnionAll, CrossJoin, Except, and Intersect amount to a computation and are not updatable in the view statement.
The modified columns are unaffected by Group By, Having, or Distinct clauses in the view statements. The top is not used anywhere in the select_statement of the View together with the Check Option With the clause in View. The previous restrictions apply to any subqueries in the from clause of the View, just as they apply to the View itself. Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table in the view statement.
Statement 4. Partitioned Views statement
A partitioned view statement is defined by a UnionAll of member tables structured in the same way but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.
Note. The preferred method for partitioning data locally to one server is partitioned tables in view statements.
In designing a partitioning scheme, it must be clear what data belongs to each partition.
For example, the data for the CustomerOrders table is distributed in three member tables in three server locations.
CustomerOrders on Server1, CustomerOrders1 on Server2 and CustomerOrders2 on Server3. A partitioned view on Server1 is defined in the following way
--Partitioned view as defined on Server1
CREATE VIEW CustomersOrders
AS
--Select from local member table.
SELECT *
FROM CompanyData. CustomerOrders1
UNION ALL
--Select from member table on Server2.
SELECT *
FROM Server2.CompanyData.CustomerOrders2
UNION ALL
--Select from member table on Server3.
SELECT *
FROM Server3.CompanyData.CustomerOrders3;
The above query Create View "CustomersOrders" And Select the Table Name "CompanyData.CustomerOrders1" And Add statement UNIONALL And Select the Table from "Server2.CompanyData.CustomerOrder2" And Server3.CompanyData.CustomerOrders3
Generally, a view is said to be a partitioned view if it is of the following form.
SELECT <select_list1>
FROM OrderDetail1
UNION ALL
SELECT <select_list2>
FROM OrderDetail2
UNION ALL
SELECT <select_listn>
FROM OrderDetails ;
For the above query, Select select_list1 FromOrderDetail1 and Union All And Select the Table.
Statement 5. Conditions for creating partitioned Views statement
Partitioned views can be created in SQL Server to simplify the management of large tables that are partitioned. The conditions for creating partitioned views are as follows:
- The base table must already be partitioned.
- The columns used for partitioning the base table must be included in the partitioned View's SELECT statement.
- The partition scheme of the partitioned View must match the partition scheme of the base table.
- The partition function used by the partitioned View must be the same as that used by the base table.
- The partitioned View must not have any joins, subqueries, or aggregate functions in its SELECT statement.
- The partitioned View must not reference any other partitioned views or tables.
- The partitioned View must have the same columns, data types, and nullability as the base table.
It is important to note that partitioned views are not supported in all SQL server editions. They are only available in Enterprise, Developer, and Evaluation editions.
The same column cannot be used multiple times in the select list.
Statement 6. Conditions for modifying data in partitioned views
The following restrictions apply to statements that modify data in partitioned views
- The Insert statement supplies values for all the columns in the View, even if the underlying member tables have a Default constraint for those columns or if they allow for null values. For member table columns with Default definitions, the statements cannot explicitly use the keyword Default.
- The value inserted into the partitioning column satisfies at least one of the underlying constraints; otherwise, the insert action will fail with a constraint violation.
- Update statements cannot specify the Default keyword as a value in the Set clause, even if the column has a default value defined in the corresponding member table in the view statement.
- Columns in the View that are identity columns in one or more member tables cannot be modified using an Insert or Update statement.
- If one of the member tables contains a timestamp column, the data cannot be modified using an Insert or Update statement.
- If one of the member tables contains a trigger or an On Update Cascade/set Null/Set Default or On Delete Cascade/Set Null/Set Default constraint, the View cannot be modified in the view statement.
- Insert, Update, and Delete actions against a partitioned view are not allowed if there is a self-join with the same View or any of the member tables in the statement.
- Bulk importing data into a partitioned view is unsupported by BCP or the Bulk Insert and Insert ... Select * from openrowset in view statements.
However, using the Insert statement, you can insert multiple rows into a partitioned view.
Note. To update a partitioned view, the user must have Insert, Update, and Delete permissions on the member tables in the view statement.
Statement 7. Additional conditions for distributed partitioned views statements
For distributed partitioned views (when one or more member tables are remote), the following additional conditions apply,
- A distributed transaction will be started to guarantee atomicity across all nodes affected by the update in View.
- Set the xact_abort set the option to on for insert, update, or delete statements in View to work.
- Any columns in remote tables of type small money referenced in a partitioned view are mapped as money. Therefore, the corresponding columns (in the same ordinal position in the select list) in the local tables must also be of type order details.
- Under database compatibility level 110 and higher, any columns in remote tables of type smalldatetime referenced in a partitioned view are mapped as smalldatetime. Corresponding columns (in the same ordinal position in the select list) in the local tables must be smalldatetime. This is a change in behavior from earlier versions of SQL Server in which any columns in remote tables of type smalldatetime referenced in a partitioned view are mapped as datetime and corresponding columns in local tables must be of type datetime.
- Any linked server in the partitioned View cannot be a loopback linked server. This linked server points to the same instance of SQL Server.
The setting of the set row count option is ignored for insert, update, and delete actions that involve updatable partitioned views and remote tables.
When the member tables and partitioned view definitions are in place, the SQL Server query optimizer builds intelligent plans that use queries efficiently to access data from member tables. With the check constraint definitions, the query processor maps the distribution of key values across the member tables in the view statement. When a user issues a query, the query processor compares the map to the values specified in the where clause and builds an execution plan with minimal data transfer between member servers. Therefore, although some member tables may be located in remote servers, the instance of SQL Server resolves distributed queries so that the amount of distributed data that has to be transferred is minimal in View statements.
Statement 8. Using a simple create view statement
The following example creates a view by using a simple select statement. A simple view is helpful when a combination of columns is queried frequently in creating a view statement. The data from this View comes from the OrderNameDetails and OrderDetails tables of the sample database. The data provide name and hire date information for the OrderDetails of OrderNameDetails.
CREATE VIEW OrderNameDetails
AS
SELECT OrderId,OrderName,orderAddress
FROM OrderDetails e
JOIN EmployeeDetail AS p ON OrderId = p.EmpId ;
GO
The above query Create View "OrderNameDetails" And Select the column name is OrderId, OrderName, orderAddress in Table Name "OrderDetails" and add join to EmployeeDetails on column name OrderId and EmpId.
Statement 9. Using with Encryption view statement
The following example uses the Encryption option and shows the view table's computed columns, renamed columns, and multiple columns.
CREATE VIEW OrderNameDetail1
AS
SELECT OrderId,OrderName,orderAddress
FROM OrderDetails e where OrderId>1 AND OrderDate> CONVERT(datetime,'20190630',101)
GO
The above query Create view "OrderNameDetails" And Select the column name is OrderId, OrderName, orderAddress in Table Name "OrderDetails" And use Where Clause in OrderID >1 And DateTime >Conver to Datetime statement.
Statement 10. Using with check option in the view statement
The following example shows a view named EmployeeId that references two tables.
CREATE VIEW OrderDetails
AS
SELECT *
FROM OrderDetails e
INNER JOIN EmployeeDetail p
ON p.EmpId = e.OrderId
WHERE EmpId = 2
WITH CHECK OPTION ;
GO
The above query Create View "OrderDetails" And Select the OrderDetails and add the InnerJoin to Table EmployeeDetail on Column Name empId and OrderId And Add Where EmpId=2
Statement 11. Using built-in functions within a view statement
The following example shows a view definition that includes a built-in function. When using functions, you must specify a name for the derived column in this statement.
CREATE VIEW OrderDetails1
AS
SELECT TOP (100) OrderId
FROM OrderDetails
WHERE OrderDate > CONVERT(DATETIME,'20191231',101)
GROUP BY OrderId ;
GO
The above query Create View "OrderDetails1" And Select the Top hundred records in OrderDetails, and OrderDate is converted to Date time Group by OrderId statement.
Statement 12. Using partitioned data in the view statement
The following example uses tables named OrderDetail1,OrderDetail2,OrderDetail3 and OrderDetail4.
Syntax
--Create the tables and insert the values.
The above query First statement Create Table Name "OrderDetail1" and Column name is OrderId, OrderName And SELECT the Top hundred records in OrderDetails and OrderDate is convert to Date time Group by OrderId statement.
Statement 13. Creating a simple view statement
The following example creates a view by selecting only some of the columns from the OrderDetails table.
CREATE VIEW CurrentOrder AS
SELECT OrderName,orderAddress
FROM OrderDetails ;
The above query Create View "CurrentOrder" And Select the column name OrderName and orderAddress from table name "OrderDetails."
Statement 14. Create a view by joining two tables statement.
The following example creates a view by using a statement with an Outer Join. The results of the join query populate the view statement.
CREATE VIEW OrderViewDetails
AS
SELECT OrderName,orderAddress
FROM OrderDetails AS fis
LEFT OUTER JOIN EmployeeDetail AS dst
ON (fis.OrderId=dst.EmpId);
The above query Create View "OrderViewDetails" And Select the column name OrderName, OrderAddress from table name "OrderDetails" and add the Left Outer Join in EmployeeDetail table Name from Column name OrderId and EmpIid.
Conclusion
In this article, you learned how to use a SQL View statement with various options.
FAQS
Q- What is a SQL View statement?
A- A SQL View statement is a query that creates a virtual table based on the result set of a Select statement. It is a named query stored in the database that can be used as a table in subsequent queries.
Q- Why would I use a SQL View statement?
A- SQL View statements can simplify complex queries by creating a reusable, virtual table for multiple queries. They also provide a level of abstraction between the user and the underlying database tables, making it easier to change the underlying data schema without affecting the queries.
Q- Can I create a SQL View statement that references another view?
A- Yes, you can create a SQL View statement referencing another view as if it were a table. However, keep in mind that the performance of the query may be impacted by the complexity of the view hierarchy.
Q- Are there any limitations to using SQL View statements?
A- Yes, there are some limitations to using SQL View statements. For example, views may not perform as well as tables for large datasets, and some databases may have limits on the number of views that can be created. Additionally, some features may not be available in views, such as certain types of subqueries or temporary tables.
Q- Can I modify data using a SQL View statement?
A- It depends on the view definition. If the View is based on a single table, you can modify data using the View as if it were a table. However, if the View is based on multiple tables or contains aggregate functions, you may not be able to modify data using the View.