To create a View in SQL Server, explore the Database and right click the Views folder and click New View…
That shows an Add Table dialog box like below
Now click on the table we want to create View for, in this example we are going to select Accounts and PersonalDetails table by holding the ctrl (from keyboard) key and clicking on the table name. Now click on Add button.
Now, click Close button. As the PersonalDetailsId column of PersonalDetails and Accounts table are same (and relationship is created) so we will see a relationship between these two columns.
Now, check the columns checkbox from the list of tables selected from the 1st panel that will add those columns into the 2nd panel and corresponding SQL statements are written into the 3rd panel. When we want to see the result of the query automatically built after selecting the relationship and column names, we can click on “!” icon from the toolbar at the top-left and see the result in the 4th panel.
If we want to create another relationship between the column names of selected tables, we can click on the column of the table and hold and drag to another table column and leave it.
Once everything is done and we are happy with the result shown in the 4th panel, save the view by clicking on the Save icon from the toolbar in the top-left or from File menu (write the View name to save).
Now, refresh the Views folder again and we will see the View just created. To see the result of this Views, right click the View name and choose “Select Top xxx Rows” that will write the SQL select element for this View as if it is a database table and show the data in the below panel.
In this view result, notice that without writing the Joins statement between PersonalDetails and Accounts table, we are able to get columns of PersonaDetails and Accounts table because these join statements are already written when we had created the View.
In the same way, we can create a new view for very complex SQL Select statement and refer that view instead of writing that complex SQL statement every time.
It is not mandatory to prefix the view name with “view” or “vw” (as shown above – viewPersonalAccounts) however it is recommended so that by reading name, we can understand that this is a View or a physical table. The way to work with physical database tables and views in the SELECT queries are same so there must be some identifiable difference in the name; so that a developer can easily know whether the object he is working with is a database table or view.Views: 5079 | Post Order: 38