How does views work in MS SQL

[tyger]

I have been told that views in MS SQL aren’t very good to use. Let’s say that you have a view X with 2 columns Car and Color that looks like this

Car Color
----- ------
Alfa Yellow
Volvo Yellow
Volvo Blue
Ford Yellow
Ford Blue

If you then run the following statement to get all Fords that are yellow:

select * from X where Car=’Ford’ and Color=’Yellow’

I have been told that when you run this statement the database will return all rows where the Car is Ford and all rows where the Color is Yellow and then the client filter out the Fords that are yellow, that is:

This is returned to the client side:

Alfa Yellow
Volvo Yellow
Ford Yellow
Ford Blue

Then the filtering is done on the client side so I only get the Ford that is yellow. I was told that I shouldn’t use the views cause of this.

I think this sounds very strange and can’t believe that views work in this way. I thought all processing was done in the database and then only returns what I want, the yellow Ford.

Could anyone clarify this for me? Give me some links where I can read about this?

Thanks in advance
Markus

[jwshepherd]

Views are basically a few tables group together by a server side query.
select * from X where Car=’Ford’ and Color=’Yellow’
would return only Ford = Yellow

select * from X where Car=’Ford’ or  Color=’Yellow’

would return

Alfa Yellow
Volvo Yellow
Ford Yellow
Ford Blue

You can create a view by creating a sql query on the server, since it runs on the server, it is usually fasterthan the client requesting x number of tables and y pieces from each one.

I believe the information you recieved may be a little inaccurate

[pclfw]

Views work VERY well in MS SQL and can do some very detailed and complex discrimination and manipulation. Have a good look at Books-On-Line and play with them for a while.

[dioioib]

I know this thread is very old but my question is why is your query using a logical 'OR' and not the appropriate logical 'AND'? If you want all cars that are fords and yellow. Why would you request all cars that are fords or yellow?

SELECT * FROM X WHERE Car=’Ford’ AND Color=’Yellow’