Tutorial Gateway

Views in SQL Server

by

A View is a virtual table, which consists of a columns, rows from the referenced table. Unless, we defined indexed views, a view does not store a set of values in a database. When you use the SELECT Statement against a view, then the records will come from the table that we referenced while creating a view. In this article we will explain you, How to create, modify, rename, and delete Views in SQL Server.

Create View in SQL Server

You can use Transact-SQL Query, or SQL Server Management Studio to create the views in SQL Server. Before we get into the example, You can use a View for the following purposes:

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

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

Create SQL View using SQL Server Management Studio

In this example we will show you the steps involved in create a SQL View using the SQL Server Management Studio. Following are the restrictions we have to remember, while creating views, and they are:

  • A View can be created only in the current database
  • SQL Server View allows us to have maximum of 1024 columns

In order to view the existing Views, Please select the Database that has the views. From the below screenshot you can observe that, our [SQL Tutorial] database has no views.

Create Views in SQL Server 1

Right click on the Views folder will open the context menu. Please select the New View.. option from the context menu as shown below.

Create Views in SQL Server 2

Once you click on the New View… option it will open the Query Designer in separate window, and a Pop up window to add the required tables.

Create Views in SQL Server 3

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

Create Views in SQL Server 4

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

Create Views in SQL Server 5

In this example we selected 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.

Create Views in SQL Server 6

Sort Type property is used to apply SQL ORDER BY functionality. Within the Grid Pane, Please change the Sort Type to Ascending or Descending as per you requirement. In this example we are sorting Yearly Income in the Descending Order.

Create Views in SQL Server 7

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

Create Views in SQL Server 8

From the below screenshot you can see the final query that we designed

Create Views in SQL Server 9

Let us execute the query that we designed using the SQL Server Management Studio and see the result

SQL CODE

-- SQL View Example
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

OUTPUT

Create Views in SQL Server 13

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

Create Views in SQL Server 10

That will open the pop up window called Choose Name to change the default name.

Create Views in SQL Server 11

The following screenshot will show you the View that we created, and expand it to see the Column names.

Create Views in SQL Server 12

Create SQL View using Transact-SQL Query

In this example we will show you, How to create a View in SQL Server using the Create View Statement

SQL CODE

-- SQL View Example 
USE [SQL Tutorial]
GO
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 Output

SQL CODE

-- SQL View Example
USE [SQL Tutorial]
GO
SELECT * FROM [dbo].[EmployeesViewbyQuery]

OUTPUT

Create Views in SQL Server 14

Rename Views in SQL Server

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

Rename SQL View using SQL Management Studio

In this example we will show you, How to rename the SQL View using the Microsoft SQL Server Management Studio (SSMS). In order to rename, Please navigate to the View that you want to modify (EmployeesViewbySSMS), and right click on the view will open the context menu. Here, select the Rename option as shown below

Create Views in SQL Server 15

Once you click on the Rename option, SSMS allows us to rename as per our requirements.

Create Views in SQL Server 16

Rename SQL View using Transact-SQL Query

In SQL Server, we can use the sp_rename stored procedure to rename the SQL view. The basic syntax behind this is:

Syntax

SP_RENAME View_Old_Name, View_New_Name

In this example we will show you, How to rename the existing view using sp_rename stored procedure. To do so, Click New query and return the following query.

SQL CODE

-- Rename SQL View
USE [SQL Tutorial]
GO
SP_RENAME EmployeesViewbyStudio, EmployeesViewbySQLSMS

Modify Views in SQL Server

The following examples will help you understand the steps involved in modifying the SQL Views using the Transact-SQL Query, and Microsoft SQL Server Management Studio (SSMS). Before we get into the example, let us see the limitations, or restrictions to follow:

  • Dependency Objects such as triggers, or stored procedures will not be effected by modifying a view.
  • You can use the ALTER VIEW statement on indexed views because, ALTER VIEW will unconditionally drops all the indexes on the views.

Modify Views in SQL Server using SSMS

In order to modify the Views using the SQL Server Management Studio, Please navigate to the view that you want to modify (EmployeeViewbySQLSMS), and right click on the view name will open the context menu. Here, select the Design option as shown below

Create Views in SQL Server 17

Once you select the design option, a new design query window will be opened with existing tables, along with the relationship. You can modify as per your requirement.

Create Views in SQL Server 18

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

Create Views in SQL Server 19

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

SQL CODE

-- Modified SQL View
USE [SQL Tutorial]
GO

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[DepartmentName]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [dbo].[EmployeesViewbySQLSMS]
GO

OUTPUT

Create Views in SQL Server 20

Use Query to Modify Views in SQL Server

In this example, we will show you the steps involved in modifying the existing view using ALTER VIEW statement. To do so, Click New query and return the following query.

SQL CODE

-- Modified SQL View
USE [SQL Tutorial]
GO

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
GO

Let us see the Output

SQL CODE

-- Modified SQL View
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[DepartmentName]
      ,[YearlyIncome]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[EmployeesViewbySQLSMS]

OUTPUT

Create Views in SQL Server 21

From the above screenshot you can observe that, View is returning top 10 records

Find definition Of Views in SQL Server

In this example we will show you, how to use the sp_helptext stored procedure to get the definition of any view in SQL server.

SQL CODE

-- SQL View Definition
USE [SQL Tutorial]
GO
SP_HELPTEXT [EmployeesViewbyQuery]

OUTPUT

Create Views in SQL Server 22

Delete Views in SQL Server

The following examples will show you, How to delete the SQL View using the Microsoft SQL Server Management Studio (SSMS), and Transact-SQL Query. Before we get into the examples, remember:

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

Delete SQL Views using SQL Server Management Studio

In this example we will show you, How to delete a view using the SQL Server Management Studio. In order to do so, Please navigate to the view that you want to delete, and right click on the view name to open the context menu. You can click on the Delete option as shown below

For this demonstration we want to delete EmployeesViewbySQLSMS

Create Views in SQL Server 23

Once you select the delete option, a Delete Object window will be opened as shown below. Click on the show Dependencies button to check the dependencies, and then click OK to delete the view.

Create Views in SQL Server 24

Delete Views using SQL Query

In this example we will show you, How to delete a SQL View using the T-SQL query.

SQL CODE

-- Delete SQL View 
USE [SQL Tutorial]
GO

DROP VIEW [dbo].[EmployeesViewbyQuery]
GO

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

Thank You for Visiting Our Blog

Placed Under: SQL