T-SQL Tutorial

T-SQL Views


A T-SQL view is a virtual table and is created by a select composed of one or more tables.
A T-SQL view is composed of an SQL query.

Types of Views

Basic user-defined view is a query from one or more tables or from other views.
Indexed Views are views that has been materialized.
Partitioned Views are views that has partitioned data from a set of member tables across one or more servers.
System Views are stored in Object Catalog Views and uses to return information about the SQL Server database instance or the objects defined.

Create View

The T-SQL statement Create View is used to create views.

CREATE VIEW view_name
AS
SQL statement
GO


Alter View

The T-SQL statement Alter View is used to modify an existing view.

ALTER VIEW view_name
AS
SQL statement
GO


Modify Rows From View

You can modify view records using the insert, update or delete commands.

INSERT INTO view_name (column1, column2) VALUES (value1, value2);
UPDATE view_name SET column1=value1, column2=value2 WHERE column3=value3;
DELETE FROM view_name WHERE column1=value1;


Rename View

To rename view uses sp_rename command.

sp_rename 'OLD_VIEW_NAME', 'NEW_VIEW_NAME';

Drop View

To delete a view is used the command DROP VIEW.

DROP VIEW View_Name;

Views in Object Catalog Views

To check for a view information uses T-SQL Object Catalog Views.

select * from sys.views;
select * from sys.all_views where name='View_Name';
select * from sys.system_views where name='View_Name';
select * from sys.objects where type='VIEW';