SQL Server Views|sql tutorial for experienced| sql server views performance| sql server views vs tables|How to create a view in SQL Server Management Studio|Types of views in SQL Server

 SQL Server Views

SQL Server Views|sql tutorial for experienced| sql server views performance| sql server views vs tables|How to create a view in SQL Server Management Studio|Types of views in SQL Server

Summary: In this tutorial, you will learn about views and how to manage views such as creating a new view, removing a view, and updating data of the underlying tables through a view.

When you use the Select statement to query data from one or more tables, you get a result set

For example, the following statement returns the product name, brand, and list price of all products from the tbl_customer and tbl_MemberType tables:

Query:  select ISNULL(CU.FirstName,'') [First Name],ISNULL(CU.LastName,'') [Last Name],ISNULL(CU.MembershipId,'') [Membership Id],ISNULL(MT.MemberType,'') [Member Type] from tbl_Customer CU WITH(NOLOCK) JOIN tbl_MemberType MT WITH(NOLOCK) ON MT.MemberTypeId=CU.MemberTypeId

Next time, if you want to get the same result set, you can save this query into a text file, open it, and execute it again. SQL Server provides a better way to save this query in the database catalog through a view. A view is a named query stored in the database catalog that allows you to refer to it later.

So the query above can be stored as a view using the Create View statement as follows:

Create View Vw_GetCustomerDetails

As

select ISNULL(CU.FirstName,'') [First Name],ISNULL(CU.LastName,'') [Last Name],ISNULL(CU.MembershipId,'') [Membership Id],ISNULL(MT.MemberType,'') [Member Type] from tbl_Customer CU WITH(NOLOCK) JOIN tbl_MemberType MT WITH(NOLOCK) ON MT.MemberTypeId=CU.MemberTypeId

And now you are going to get Customer Details from View:

Query: Select * from Vw_GetCustomerDetails

When receiving this query, SQL Server executes the following query:

Select * from (

select ISNULL(CU.FirstName,'') [First Name],ISNULL(CU.LastName,'') [Last Name],ISNULL(CU.MembershipId,'') [Membership Id],ISNULL(MT.MemberType,'') [Member Type] from tbl_Customer CU WITH(NOLOCK) JOIN tbl_MemberType MT WITH(NOLOCK) ON MT.MemberTypeId=CU.MemberTypeId

)

By definition, views do not store data except for indexed view.

A view may consist of columns from multiple tables using joins or just a subset of columns of a single table. This makes views useful for abstracting or hiding complex queries.

Advantages of views:

Security

You can restrict users to access directly to a table and allow them to access a subset of data via views.

For example, you can allow users to access customer name, phone, email via a view but restrict them to access the bank account and other sensitive information.

Simplicity

A relational database may have many tables with complex relationships e.g., one-to-one and one-to-many that make it difficult to navigate.

However, you can simplify the complex queries with joins and conditions using a set of views.

Consistency

Sometimes, you need to write a complex formula or logic in every query.

To make it consistent, you can hide the complex queries logic and calculations in views.

Once views are defined, you can reference the logic from the views rather than rewriting it in separate queries.

Managing views in SQL Server

• Creating New View– show you how to create a new view in a SQL Server database.
• Remaining a View– learn how to rename a view using the SQL Server Management Studio (SSMS) or Transact-SQL command.
• Listing views in sql server– discuss the various way to list all views in a SQL Server Database.
• Getting view information– how to get information about a view.
• Removing a view– guide you how to use the DROP VIEW statement to remove one or more views from the database.
• Creating an indexed view – show you how to create an indexed view against tables that have infrequent data modification to optimize the performance of the view.