Posted by gherrell on 08/15/06 17:26

I know there is a lot of information already out there on this topic,
but given the following scenario...

--------------------------------------------------------------------------------------------------------------------------------------
Create a view like so ( pardon the pseudo-code )...

CREATE View vwContactAddresses
Select * FROM Contact INNER JOIN Address ON Contact.ContactID =
Address.ContactID

And then do a sargable select from the view using a stored procedure

CREATE STORED PROCEDURE spSelect_ContactAddresses
@ContactID int
AS
Select * FROM vwContactAddresses WHERE ContactID = @ContactID
--------------------------------------------------------------------------------------------------------------------------------------

In my understanding, "vwContactAddresses" would be substituted with the
actual SQL join statement when the view is accessed.

So for the stored procedure in question an execution plan for
"Select * FROM Contact INNER JOIN Address ON Contact.ContactID =
Address.ContactID WHERE ContactID = @ContactID" would be cached.
Correct?

With regards to execution plan caching, is this not the same as
creating an inline UDF that takes parameters or just creating a stored
procedure that would do the join w/out the view reference?

• Next in forum: Re: Stored proc duplicating data
• Prev in forum: Re: Stored proc duplicating data
• Thread view: Views, UDFs

[Reply to this message]