In this article, we will see how to create a view programmatically in SQL Server 2012. View is defined as a virtual table with data from various tables in the database. Views are most commonly used to provide security because it ensures that a user can only access the data they are intended to access and any remaining data is neither seen nor can be accessed by them. The remaining data is thereby automatically protected from unwanted users. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
Create a view programmatically
Syntax
CREATE VIEW view_name (column_name1,column2..)
WITH ENCRYPTION
AS
select_statement [WITH CHECK OPTION ]
I have a two tables named UserDetail and UserTable. Both have a different column. Now I want to retrieve a column from the first (the UserDetail) table and some columns from the second (the UserTable) table.
First Table:
Second Table:
Now the following query defines the view:
CREATE VIEW Detail
AS
(
SELECT dbo.UserDetail.User_Id, dbo.UserDetail.FirstName, dbo.UserDetail.LastName, dbo.UserTable.ID, dbo.UserTable.Name
FROM dbo.UserDetail CROSS JOIN
dbo.UserTable
)
Now Press F5 to execute the query.
OUTPUT
ALTERING View
Using the ALTER VIEW statement you can modify a view without dropping it. We can modify the view using the following query:
ALTER VIEW view_name
AS
SELECT statement
DROPPING View
We can delete the view using the drop command as follows:
DROP VIEW view_name
DROPPING Multiple Views
You can drop a single view or multiple views at a time.
Syntax
DROP VIEW view_name1, view_name2,..