Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

Views in SQL Server

by suresh

In this study, we explain to you how to create View in SQL, 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 views, a view in SQL Server 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.

Create View in SQL Server

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

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

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 views are

  • A View can be created only in the current database
  • The view allows us to have a maximum of 1024 columns

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

Create Views in SQL Server 1

Right-click on the Views folder in Management Studio will open the context menu. To create a view in SQL Server, Please select the New View..option from it.

Create Views in SQL Server 2

Once you click on the New View… option, the Query Designer opens in a 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, 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 choose the columns needed by check-marking the column names in the Diagram Pane.

Create Views in SQL Server 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.

Create Views in SQL Server 6

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 SQL Server views 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 select query that we designed

Create Views in SQL Server 9

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

-- 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
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 popup 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

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

SQL View using Create View Statement

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

-- 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 View Example
USE [SQL Tutorial]
GO
SELECT * FROM [dbo].[EmployeesViewbyQuery]
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 management studio to rename views in SQL Server.

Rename SQL View in SQL Management Studio

This example rename the View using SSMS.

To rename the SQL View using SSMS, Please navigate to the View that you want to modify (EmployeesViewbySSMS). And right-click on the view and select the Rename option

Create Views in SQL Server 15

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

Create 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 for views is:

SP_RENAME View_Old_Name, View_New_Name

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

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

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 a view.
  • You can use the ALTER VIEW statement on indexed views because ALTER VIEW will unconditionally drop all the indexes on the views.

Modify Views using SSMS

To modify Views in Sql Server Management Studio, navigate to the view that you want to change (EmployeeViewbySQLSMS). Next, right-click on it and select the Design option

Create Views in SQL Server 17

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

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.

-- Modified SQL View
USE [SQL Tutorial]
GO

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[DepartmentName]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [dbo].[EmployeesViewbySQLSMS]
GO
Create Views in SQL Server 20

Alter View to Modify Views in SQL Server

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

-- 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

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

Find definition Of a Views

How to use the sp_helptext stored procedure to get the definition of any view?.

-- SQL View Definition
USE [SQL Tutorial]
GO
SP_HELPTEXT [EmployeesViewbyQuery]
Create Views in SQL Server 22

Delete Views in SQL Server

The following examples show how to delete Views.

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

Delete Views in Management Studio

To delete a view using the Management Studio, right-click on the view name and click on the Delete option

For this views demonstration, we want to delete EmployeesViewbySQLSMS

Create Views in SQL Server 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 the view.

Create Views in SQL Server 24

Delete Views or Drop View

Let me use the DROP VIEW statement to delete a SQL View.

-- Delete SQL View 
USE [SQL Tutorial]
GO

DROP VIEW [dbo].[EmployeesViewbyQuery]
GO

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

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy