Creating a view
How do I create a view in SQL Server 2000 ?
Answer:
The easiest way to create a view in SQL 2000 is point and click, an example:
We have a database with employees and departments in seperate tables and we want to select fields from both tables throughout our website, instead of constantly creating JOIN queries we setup a simple view.
First we expand our database, then we rightclick on Views and select New View.
We are now in the Design View window and here we click on the button "Add table".
We select the tables we want fields from into our view and we click on Add.
After clicking close we simply check the fields we want in our view and that is how you create a view.
For completeness I'm making this a sorted view, this view sorts on Department name.
This is how the sort (ORDER BY) looks like in the table windows.
Now close this window and name your view, I called it viewEmployees.
This is the completed Query that SQL stores for it's own use, you would use this query everytime you wanted this data in your webpages:
SELECT TOP 100 PERCENT dbo.Employees.EmployeeID, dbo.Employees.EmployeeName, dbo.Employees.Extension, dbo.Departments.DeptName
FROM dbo.Departments INNER JOIN dbo.Employees ON dbo.Departments.DeptID = dbo.Employees.DeptID
ORDER BY dbo.Departments.DeptName
Now you simply do a Select * FROM viewEmployees to get the same data, it's cleaner this way and the query has become a re-usable object as well.
Note: In order to use this view it needs the proper permissions, if you do not know how to set permissions then please check here: http://www.dmxzone.com/go?4130
Comments
Be the first to write a comment
You must me logged in to write a comment.