Create Views in SQL Server 13

Views in SQL Server

In this study, we explain to you how to create, modify, rename, and delete Views in SQL Server. The SQL Views are the virtual tables, which consist of columns, rows from the referenced table.

Unless we defined indexed, a view in SQL Server does not store a set of values in a database. When you use the SELECT Statement against a SQL view, then the records will come from the table that we referenced while creating.

Create View in SQL Server

You can use Transact sql query or Management Studio to create or replace views. Before we get into the example, You can use them for the following purposes:

  • To simplify the data as per the user needs.
  • Restrict the Users not to access the entire database.

SQL Create View Statement

This example shows how to create View in SQL Server using the Create Statement

CREATE VIEW EmployeesViewbyQuery
AS
SELECT  [FirstName] + ' ' + [LastName] AS Name
	,[Occupation]
	,[Education]
	,dept.DepartmentName AS Department
	,[YearlyIncome] AS Income
	,[Sales]
FROM [MyEmployees Table]
   INNER JOIN 
 	 Department AS dept ON
	    Dept.[id] = [MyEmployees Table].DeptID
GO

Let us see the rows and columns result set

SELECT * FROM [dbo].[EmployeesViewbyQuery]

Create View in SQL Server Management Studio

This example shows the steps involved to create View in SQL Server using the Management Studio. The restrictions to remember while creating are

  • It can be created only in the current database
  • It allows us to have a maximum of 1024 columns

To see the existing Sql Server Views, Please select the Database that has it. As you see, our database has none.

Right-click on the folder in Management Studio will open the context menu. To create it, Please select the shown option from it.

Create Views in SQL Server 2

Once you click on the option, the Query Designer opens in a separate window, and a Pop-up window to add the required tables.

Add Tables to Query Designer

Here, you can add functions, tables, or views in SQL Server. From the below, you can observe that we selected the MyEmployees table and Department table.

Select Table 4

Once you selected the required tables, it will show the Query Designer. Please choose the columns needed by check-marking the column names in the Diagram Pane.

Query Designer to develop a view 5

In this SQL Server example, we chose the First Name, Last Name, Education, Occupation, Department, Yearly Income, Sales, Hire Date from two tables. Remember, you can Join two tables by dragging one column on to the other.

Choose Columns for your View Query

The sort Type property is used to apply for ORDER BY functionality. Within the Grid Pane, Please change the Sort Type to Ascending or Descending. In this example, we are sorting Yearly Income in the Descending Order.

Once you selected the Descending Operation, it will show you the sorting representation

Create Views in SQL Server 8

The final select query that we designed

Create Views in SQL Server 9

Let us execute this Sql views query that we designed using the Management Studio and see the result

SELECT  TOP (100) PERCENT dbo.[MyEmployees Table].FirstName, 
        dbo.[MyEmployees Table].LastName, 
        dbo.[MyEmployees Table].Education, 
        dbo.[MyEmployees Table].Occupation, 
        dbo.Department.DepartmentName, 
        dbo.[MyEmployees Table].YearlyIncome, 
        dbo.[MyEmployees Table].Sales, 
        dbo.[MyEmployees Table].HireDate
FROM dbo.Department
 INNER JOIN
     dbo.[MyEmployees Table] ON 
         dbo.Department.id = dbo.[MyEmployees Table].DeptID
ORDER BY dbo.[MyEmployees Table].YearlyIncome DESC
Select Statement on Views

Next, go to File Menu and select the Save option to save the designed query.

It will open the popup window called Choose Name to change the default name.

Choose a Name for View

Expand the folder, see the one that we created, and expand it to see the Column names.

View Column Names in Views 12

NOTE: Some clients give access to Views only, and they will not allow you to access their main database.

Rename Views in SQL Server

The SQL Server allows you to use the built-in stored procedure sp_rename, or the management studio to rename views.

Rename in Management Studio

This example rename them using SSMS. And to rename it, Please navigate to the one that you want to modify. And right-click on the name and select the Rename option

Rename View in management Studio

Once you click on the Rename option, SSMS allows us to rename them.

Rename Views in SQL Server 16

Rename SQL View using SP_RENAME

We can use the sp_rename stored procedure to rename the view. The sp_rename syntax is:

SP_RENAME Old_Name, New_Name

Using the sp_rename stored procedure, you can rename the existing. To do so, Click New query and return the following query.

SP_RENAME EmployeesViewbyStudio, EmployeesVwName

Modify Views in SQL Server

The following examples help you understand the steps involved in modifying the SQL Views. First, let us see the limitations or restrictions to follow:

  • Dependency Objects such as triggers or stored procedures will not affect by modifying.
  • You can use the ALTER VIEW statement on indexed ones because it will unconditionally drop all the indexes.

Modify in SSMS

To modify in Management Studio, navigate to the one that you want to change. Next, right-click on it and select the Design option

Design Views in Management Studio 17

It opens a new design query window with existing tables, along with the relationship.

Here, we are using the Filter (WHERE Clause) to restrict the Employees whose Occupation = Professional, and we are using two columns in ORDER BY Clause

Modify Views in SQL management Studio 19

Let me select all the records from the below to show the modified records.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[DepartmentName]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [dbo].[EmployeesViewbySQLSMS]
Select Records from Modified Views 20

Alter View to Modify

The SQL Server ALTER VIEW statement is an ideal approach to modify the existing ones. To do so, click the new query and write the subsequent query.

ALTER VIEW [dbo].[EmployeesViewbySQLSMS]
AS
SELECT TOP 10 emp.FirstName, 
          emp.LastName, 
   emp.Education, 
   emp.Occupation, 
   Dept.DepartmentName, 
          emp.YearlyIncome, 
   emp.Sales
FROM  dbo.Department AS Dept
   INNER JOIN
      dbo.[MyEmployees Table] AS emp ON 
        Dept.id = emp.DeptID
ORDER BY emp.YearlyIncome DESC, 
         emp.Sales DESC

Let us see the Output

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[DepartmentName]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeesViewbySQLSMS]
Modify Views in SQL Server 21

How to find definition Of the Views?

How to use the sp_helptext stored procedure to get the definition of Views in SQL Server.

SP_HELPTEXT [EmployeesViewbyQuery]
Find Definition of a View 22

Delete Views in SQL Server

The following examples show how to delete it.

  • Deleting, or Dropping a Table will not drop the dependent View. You have to explicitly use the SQL Server DROP VIEW statement to delete them.
  • When you drop it, all the information (including the definition) will delete from the system catalog.

Management Studio

To delete using the Management Studio, right-click on the name and click on the Delete option. For this demonstration, we want to delete the below one.

Delete Views in Management Studio 23

Selecting the delete option will open a Delete Object window. Click on the Show Dependencies button to check the dependencies, and then click OK to delete.

Delete View Object 24

Delete or Drop Views

Let me use the DROP statement to delete Views in SQL Server.

DROP VIEW [dbo].[EmployeesVwbyQuery]
GO

TIP: It is good practice to check whether it exists in the database or not using IF OBJECT_ID (N’VwName’, ‘V’) IS NOT NULL

Comments are closed.