• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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?
0
AJS_Developer
Asked:
AJS_Developer
  • 2
1 Solution
 
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..
0
 
AJS_DeveloperAuthor Commented:
Thanks rrjegan17 for confirming my assumption. And that would mean the second statement is much quicker, correct?
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now