Views, UDFs

Giganews Newsgroups
Subject: Views, UDFs
Posted by:  gherrell (greg.herre…@gmail.com)
Date: 15 Aug 2006

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?

Replies