SQL Server 2005 – What is a View?

I explained that a couple of times lately so I decided to write a detailed description of a View in SQL Server 2005. Here we go:

There will be times when you want to group together data from more than one table, or perhaps only allow users to see specific information from a particular table, where some of the columns may contain sensitive or even irrelevant data. A view can take one or more columns from one or more tables and present this information to a user, without the user accessing the actual underlying tables. A view protects the data layer while allowing access to the data. All of these scenarios can be seen as the basis and reason for building a view rather than another method of data extraction. If you are familiar with MS Access, views are similar to Access queries. Because a view represents data as if it was another table, a virtual table in fact, it is also possible to create a view of a view.

Let’s take a look at how a view works. For example we have a customer table that holds information about customers such as their first name, last name, account number, and
balances. There will be times when you want your users to have access to only the first and last names, but not to the other sensitive data. This is where a view comes into play. You would create a view that returns only a customers first and last name but no other information. Creating a view can give a user enough information to satisfy a query he or she may have about data within a database without that user having to know any T-SQL commands. A view actually stores the query that creates it, and when you execute the view, the underlying query is the code that is being executed. The underlying code can be as complex as required, therefore leaving the end user with a simple SELECT * command to run with perhaps a small amount of filtering via a simple WHERE statement.

From a view, in addition to retrieving data, you can also modify the data that is being displayed, delete data, and in some situations insert new data. There are several rules and limitations for deleting, modifying, and inserting data from multitable views, some of which will be covered in the “Indexing a View” section later in the chapter.

However, a view is not a tool for processing data using T-SQL commands, like a stored procedure is. A view is only able to hold one query at a time. Therefore, a view is more like a query than a stored procedure. Just as with a stored procedure or a query within a Query Editor pane, you can include tables from databases that are running on different servers. Providing the user ID has the necessary security credentials, it is possible to include tables from several databases.

So to summarize, a view is a virtual table created by a stored SQL statement that can span multiple tables. Views can be used as a method of security within your database, and they provide a simpler front end to a user querying the data.

Hope that helps..

5 comments ↓

#1 Julio on 04.06.06 at 9:45 am

Great Article Andreas! Did you check the latest build of my SQL Tool already? Added some new features 😉

#2 Julia on 04.30.07 at 12:17 pm

Thanks for the great work! It really is more than what was expected. I loved its simplicity! Keep up the good work!

#3 Vinay on 11.06.08 at 6:21 am

Here is my question . after creating view and do the insert operation will it affect to the main table or not?

#4 kunal on 02.23.09 at 10:58 am

it helps me,good information are given but practical example gives more explanatory.

#5 Nitin Choudhary on 04.06.12 at 10:27 am

Thanx a lot for sharing great information…

Leave a Comment