Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Kapil's Blog

Kapil is a a Database Developer working in IT industy for the last 2 years. He has experience writing SQL Queries, SQL Server, ETL, SSIS, SSRS, Database backups & restoring, managing database, scheduling jobs.

Create a VIEW



Creating a View
This is my first blog in field of SQL which I am starting with topic  creating a VIEW. In this article first  I will give you guys a brief description of View and explain how to create a simple Views.
As we know Views is just simply a SELECT statement  or like a virtual table. Its advantage is that once  it get created it acts like a table for any other SELECT statement that you want to write.
Syntax:
CREATE VIEW [schema_name.]view_name [Column_name…..n]
WITH [<view_Attributes> [……n]]
AS
Select_statement
[WITH CHECK OPTION][;]
A view can contain multiple SELECT statement as long as you use the UNION or UNION  ALL operators.
The view attribues that can be specified are ENCRYPTION, SCHEMABINDING and VIEW_METADATA
It is a good practice to create a view with prefic ‘vw’ as it is easy to distinguish between a table and a view. So we should used with prefix ‘vw’ to identify a view.
Suppose we want to display the all column from a table MyTable.
Lets create a table with structure:
CREATE TABLE MyTable
(
      ID INT,
      Name VARCHAR(10)
)
Lets INSERT some data into tablr MyTable :
INSERT INTO MyTable
VALUES
(1,'Mohit'),
(2,'Ajay'),
(3,'Sachin')

Now we have some data in the table that we created. Now its time to create a view for this table:
 Then we can create in this way:
CREATE VIEW vw_ShowData
AS
SELECT ID, Name FROM MyTable
Go
It always good to list all column names in a SELECT statement while creating VIEW rather then using ‘*’ with SELECT statement.
After executing this script view is created successfully.
To check that VIEW is created or not we can check in the OBJECT EXPLORER.
Database_Name > VIEWS > View_name
Under this path we can find user_defined views list. . You can the check the list of all views that exists in the database in the View option after expanding database.


Now run this statement to check whether our view is returning correct data or not.
SELECT *  FROM vw_show

This will display all the columns of the table MyTable.  
So, in this manner we have created our first simple view. In next article I will write How to UPDATE the view and DELETE the view.

Comments

Leave a comment on the original post [sqlpassionate.blogspot.com, opens in a new window]

Loading comments...