Saturday, March 22, 2014

What are 'views' in SQL Server?

Views are virtual tables created based on existing table(s). Views can have generally fewer columns than the underlying table(s). Views can be based on a single table or on multiple tables. Since you can pick and choose the columns, you can hide columns that you do not want to be seen and display only those you want. Since a view can choose from different tables (that can participate in a join), it is useful in front end design to query all the relevant tables and create a view.

For example the following query returns 91 rows of Customer data from Northwind database in SQL Server 2012 Express:

SELECT [CustomerID]
      ,[CompanyName]
      ,[ContactName]
      ,[ContactTitle]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[Phone]
      ,[Fax]
  FROM [NORTHWND].[dbo].[Customers]

Let us say your front end application needs just CompanyName, Address, City, State, Country. Then you create a query as shown:

SELECT
      [CompanyName]
      ,[Address]
      ,[City]
      ,[PostalCode]
      ,[Country]+', '+[PostalCode]
     
  FROM [NORTHWND].[dbo].[Customers]

Now create the view based on this query as shown which you want to create a mail label

Create view MailLable
as
SELECT
      [CompanyName]
      ,[Address]
      ,[City]
      ,[PostalCode]
      ,[Country]+', '+[PostalCode] as 'Country'
      FROM [NORTHWND].[dbo].[Customers]

This creates a view object in View node of NORTHWND database called MailLable as shown.

The views in the database are themselves stored in a System View and you can find all existing views using the following query:

SELECT * FROM SYS.VIEWS

There are lot more things related to views we will take them up in another post.