Difference between UDF and View

Question

Difference between UDF and View

Mahesh Bote

SSCrazy Eights

Points: 9107
More actions
May 7, 2008 at 12:37 am

#123718

[font="Verdana"]
Can anybody let me know what is(are) the difference(s) between User Defined Function and View except below one?
1. User Defined Function can accept Parameter(s). They can be optional, whereas View can not accept parameter
2 ?
3 ?
...
Thanks in advance,
Mahesh
[/font]
MH-09-AM-8694

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Views are just saved select statements. UDFs, especially the multi-statement table valued function, can have complex logic, conditional statements, loops, etc

At time of execution, the defnition of a view it places into the statement before compilation, replacing the view name.

Functions are not (usually) inlined and acna be called one or more times, depending where and how the function is used.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

2. Functions can result in poor performance.

3. Functions can result in REALLY REALLY BAD performance. 🙂

4. Both can make it very difficult to debug/maintain code.

5. Functions (or views that contain functions) can make it dangerous to do simple selects due to what can be coded inside them.

6. Did I mention that functions are often bad for performance?? 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

7. Table-valued functions return table VARIABLES, which don't generate statistics in the same way "regular" tables (even temporary ones) do (a single primary key index is possible, although damn near never used). This forces the optimizer to "guess" how many rows a TVF will be returning, and thus will often lead to bad execution plans. Which then leads to SQLGuru's point #3....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

TheSQLGuru (5/8/2008)
2. Functions can result in poor performance.
3. Functions can result in REALLY REALLY BAD performance. 🙂
6. Did I mention that functions are often bad for performance?? 😀

Especially scalar functions that affect largish numbers of rows. Especially if those functions run for every row of a large result set.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Properly used, UDF's good; improperly used, UDF's BAD.

Comes down to an "It depends".

I have a set of tvf that allow me to do As Of queries against our ODS database. If I were to need to use any of those functions in a complex query, I'd use then to populate properly indexed temp tables and then use the temp tables in the queries. For a quick and dirty check of data on a specific date, the UDF is good enough.

😎

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Lynn Pettis (5/8/2008)
Properly used, UDF's good; improperly used, UDF's BAD.
Comes down to an "It depends".
I have a set of tvf that allow me to do As Of queries against our ODS database. If I were to need to use any of those functions in a complex query, I'd use then to populate properly indexed temp tables and then use the temp tables in the queries. For a quick and dirty check of data on a specific date, the UDF is good enough.
😎

I will go with the newly-coined 95/3/2 rule. 95% of the time they are both bad AND unnecessary. 3% of the time they are one OR the other. 2% of the time they are midly acceptable :hehe:

Actually, flip that. Since I make a large percentage of my revenue each year cleaning up performance nightmares, 95% of the time functions are GREAT!!!! Hey, where is the dollar-sign smiley when you need it?!?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 7

[font="Verdana"]Thats really nice of you all. Thank you very much.

Mahesh[/font]

MH-09-AM-8694