Create alter drop views in SQL Server - Dotnetspan.com

View is most important feature of T-SQL in SQL Server. View is treat as virtual table based on result set of SQL Statement. View have rows and columns which may be from one or more table. You can perform various operations like filter, sort and custom sql statement on view. 
as per MSDN a 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 users permissions to directly access the underlying base tables.
  • To provide a backward compatible interface to emulate a table whose schema has changed.
 In this article we will discuss for Create a View, Alter a View, Update a View and drop or delete a View. We take simple example as we discuss in our every article.

Database Structure for our example
Column NameData Type
IDint(Primary Key)
First Namevarchar(50)
Last Namevarchar(50)
Cityvarchar(30)
EmailIDvarchar(100)

Sql Query
CREATE TABLE Sample(
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [FirstName] [varchar](50) NULL,
 [LastName] [varchar](50) NULL,
 [City] [varchar](30) NULL,
 [EmailID] [varchar](100) NULL)
We have Pre-Inserted data in our table
ExecuteScalar() Example in asp.net

SQL Create VIEW  

SQL Create View Syntax
CREATE VIEW [view-name] AS
SELECT [columns]
FROM [table-name]
WHERE [condition]
  • [view-name]- set any appropriate name of your View
  • [columns]- you can include one or more columns of any datatable
  • [table-name]- your table from where you are generating view. you can use one or more table by join conditions.
  • [condition]- you can include where conditions to filter your result.
We are creating a view to get combination of first name and last name as full name.
SQL Create View Example
CREATE VIEW Sample_View AS
SELECT (FirstName +' '+ LastName) as FullName 
from Sample
Select View
select * from Sample_View
Result

SQL Update / ALTER VIEW

ALTER VIEW Sample_View AS
SELECT Firstname, LastName 
from Sample
After selecting updated view we found result
create-update-alter-drop-select-view-in-sql-server-img

SQL DROP VIEW

DROP VIEW Sample_View
After Dropping current View when we try to select view it will give following error Invalid object name 'Sample_view' because it can not find any Sample_View in current database.


reference 
http://www.w3schools.com/sql/sql_view.asp
http://msdn.microsoft.com/en-IN/library/ms187956.aspx