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.
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.
CREATE VIEW [schema_name.]view_name [Column_name…..n]
WITH [<view_Attributes> [……n]]
[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
Lets INSERT some data into tablr MyTable :
INSERT INTO MyTable
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
SELECT ID, Name FROM MyTable
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
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.