SQL Server 2008 Function and Procedure design question

My question is about using Types in SQL Server 2008. Will my new attempt at updating a table using functions be much faster by using Types? New attempt described below:

I'm building a stored procedure that returns a result set with any number of columns. The columns in the result depend on arguments passed when calling the procedure.

Some of the columns I add to the temp table while building the result to be returned are based on the result of a function. Normally with SQL Server I'd write a statement such as:

UPDATE MyTable SET NewColumn = f_GetNewColumValue(MyTable.PrimaryKeyField)

but I've seen in SQL Server 2008 I can pass a TYPE to a function, which lets me write something like

UPDATE MyTable SET NewColumn = NewColumnValue FROM MyTable LEFT JOIN f_GetNewColumValue(@Keys) ncv ON ncv.PrimaryKeyField = MyTable.PrimaryKeyField

where @Keys is a type I pre-defined with 1 column for the primary key field.

I assumed this would be quicker. Will my new attempt at updating a table using functions be much faster by using Types?
AJS_DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> UPDATE MyTable SET NewColumn = f_GetNewColumValue(MyTable.PrimaryKeyField)
>> UPDATE MyTable SET NewColumn = NewColumnValue FROM MyTable LEFT JOIN f_GetNewColumValue(@Keys) ncv ON ncv.PrimaryKeyField = MyTable.PrimaryKeyField

The only difference between the two statements is that first statement would execute that function once for each and every records present in the table
Second statement would calculate all the values since it is referenced in JOIN clause and would UPDATE only once with fewer execution of that function..

Anyhow, need to check with Function code once to confirm it..
AJS_DeveloperAuthor Commented:
Thanks rrjegan17 for confirming my assumption. And that would mean the second statement is much quicker, correct?
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, Second construct would execute faster and your Function code needs to be re-written accordingly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.