SQL Server series: views

A view is a virtual table in a database that, like a real table, contains a series of row and column data with names. Row and column data are referenced by the query that is used to customize the view, and are generated dynamically when the view is referenced.

1. Concept of views

A view is exported from one or more tables and behaves like a table, but the view is a virtual table. Views can be defined on the basis of a view that already exists. On a view, the user can query the database using the SELECT statement and modify the record using INSERT, UPDATE, and DELETE statements. The operation for the view is ultimately translated into operations on the base data table.

Usage: 1. Assemble multiple tables into a single table such as the first page data display.

2. Create a View

To create a view syntax using T-sql:

CREATE VIEW [schema_name] view_name [(column [,... n])] [with <view_attribute> [,... N]] as Select_statem ent [with CHECK OPTION] [;]

Instance:

Single-Table View

CREATE VIEW [dbo]. [V_product] As    SELECT [productid],[productname] from    [dbo].[ Product]

Multi-table View

CREATE VIEW [dbo]. [V_productcategory] As    SELECT [productid],[categoryname],[productname] from    [dbo].[ Product] INNER JOIN [dbo]. [Category]    on [dbo]. [Product]. [CategoryID] = [dbo]. [Category]. [CategoryID]

Create a sort view

CREATE VIEW [dbo]. [V_product] As    SELECT TOP PERCENT [productid],[productname] from    [dbo].[ Product]    ORDER by [UnitPrice]

3. Modify the View

To modify the view syntax using T-sql:

ALTER VIEW [schema_name] view_name [(column [,... n])] [with <view_attribute> [,... N]] as Select_statem ent [with CHECK OPTION] [;]

4. View View

To view the view structure:

EXEC sp_help [V_product]

To view the view text:

EXEC sp_helptext [V_product]

5. Delete a view

T-SQL Delete view syntax:

DROP VIEW [schema_name.] view_name [..., n] [;]

To delete a single view:

DROP VIEW [V_product]

To delete multiple views:

DROP VIEW [V_product],[v_productcategory]

SQL Server series: views