SELECT on VIEW with WHERE clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT on VIEW with WHERE clause

Author

Topic

AspNetGuy
Starting Member

5 Posts

Posted - 2011-11-28 : 20:12:54

If I have a view which SELECTs all of the rows of a table and I do a SELECT against the view and include a where clause with an ID of the indexed ID column of the table, will SQL Server read into memory all of the rows and then select on the ID I provided?

Below is an example of my question.

SELECT [ProductID]
,[ProductName]
,[ProductType]
FROM [ViewTester].[dbo].[v_product]
WHERE ProductID = 1999
GO

Assumptions:
1) ProductID is the primary key.
2) A ProductID of 1999 exists in the table.
3) The SELECT in the view has NO where clause and reads as follows:

SELECT * FROM Product

My question goes to whether or not the select in the view will always read into memory all of the rows (since the select in the view has no where clause) or is Sql Server smart enough to combine the where clause

Thanks for any words of advice.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-28 : 22:22:04

quote:
Originally posted by AspNetGuy

My question goes to whether or not the select in the view will always read into memory all of the rows (since the select in the view has no where clause) or is Sql Server smart enough to combine the where clause

SQL Server is smart enough to combine the where clause.

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

AspNetGuy
Starting Member

5 Posts

Posted - 2011-11-29 : 10:53:36

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources