Create & Modify Views

First, build a view

IF object_id ('sales.ordertotalsbyyear'V' is not NULL    DROP VIEW sales.ordertotalsbyyear; GO

Example one:

CREATE VIEWSales.ordertotalsbyyear withSCHEMABINDING,--when modifying the table or view used to generate the current view, modifications are not allowed once the current view is affected (causing the view to become invalid). Encryption--encryption, cannot be edited (encryption level is not high) as    SELECT   Year(o.orderdate) asOrderYear,SUM(Od.qty) asQty fromSales.orders asOJOINSales.orderdetails asOd onOd.orderid=O.orderidGROUP  by  Year(OrderDate);GO

Example two:

CREATE VIEWDbo.viewtestindexinfo as    SELECT DISTINCTEmployees.*     fromEmployeesJOINSales onEmployees.EmployeeID=Sales.employeeidWHERETitle= 'Sales Person' with CHECK OPTION   --If there is a where statement in the view, it is possible to update the table with view to modify the row outside the Where condition, which is used to force the changed content to match the Where condition

Second, view the view in the database

--to explore view metadata using T-SQL, you can query the Sys.views catalog view: UseTSQL2012;GOSELECTNameobject_id, principal_id, schema_id, type fromsys.views;--You can also query the INFORMATION_SCHEMA. TABLES system view, but it's slightly more complex:SELECTschema_name, TABLE_NAME, Table_type frominformation_schema. TABLESWHERETable_type= 'VIEW';

Third, modify the view

ALTER VIEWSales.ordertotalsbyyear withSCHEMABINDING as    SELECTO.shipregion, Year(o.orderdate) asOrderYear,SUM(Od.qty) asQty fromSales.orders asOJOINSales.orderdetails asOd onOd.orderid=O.orderidGROUP  by  Year(OrderDate), o.shipregion;GO

Iv. Modifying records through views

-- adding records through views  If the view has the check option, the inserted data must follow the WHERE condition insert  vemployees          SELECT  3  ,                'xxx'  ,                '  xx'

Create & Modify Views