Generating a View in SQL Server 2008

In this article I will describe what a view is, how it is usefull and how you will generate a view between more than one table.

In SQL Server a view represents a virtual table. You can say it is a subset of a table. Just like a real table, a view consists of rows with columns, and we can retrieve data from a view (sometimes even update data in a view). The fields in the view's virtual table are the fields of one or more real tables in the database.

Why View?

We need to have a goal in mind when creating a view. There are a number of scenarios where we have to look for a view as a solution.

  • To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
  • To control access to rows and columns of data.
  • To aggregate data for performance.
Now in SQL Server 2008 views are useful for generating a complex query and to fetch data by giving a filter.

Step 1:

First connect your SQL Server 2008 and after that explore your Database.
 

SqlView1.gif

Step 2:

Now right-click on the "views" folder and click "new view".
 

SqlView2.gif

Step 3:

After that you will see a new "Add table" window. In the "Tables" tab please select your required table; for mine I selected "Customer" and "tblorders". After that click the "Add" button.
 

SqlView3.gif

Step 4:

After that you will see the tables with a relation diagram just like the following figure:
 

SqlView4.gif

Now look here; we got the default query between 2 tables. Now we will configure the "column" the "alias" giving "Sort Type", "Filter" etc.

Now when you change the settings the query will automatically change.
 

SqlView5.gif

See the upper figure. While checking the column name in the customer tables the column name will be shown (red arrows). After that give the Alias Name (marked with blue). Then after that give the Filter Name "Bag" on the "OrderName" part (marked with a brown square).

Now see the automatic query generated for everything. Marked with a red square.

Now when you run the application you will see the result.

Conclusion: In SQL Server when generating a view it is usefull to write the complex query in a stored procedure while there is a situation to fetch the data.