Data access by creating functions

a number of time I have come accross developers using functions like

GetProduct()
GetProductTitle()
GetProductCategory()

to get the value of the data instead of just using queries / stored procedures

I have not understood why

can you please point to some good forum messages / blogposts / articles on this ?

Whats your take on this ?Dealing with functions that give direct results is easier programmatically than dealing with OUTPUT parameters in stored procedures.|||The concept of user defined functions is a great way to centralize code and they can come in very handy.

But in my experience they have one major drawback: performance.

When a user defined function needs to be performed on over a thousend rows (arbitrary boundery) the performance seems to decrease dramaticly. My guess it is because SQL Server has to switch from set-processing to row-by-row prossing.

AmitGeorge: look in the BOL under CREATE FUNCTION. That doesn't say anything about performance though.

Lex|||The use of UDFs will not cause the engine to stop using set-based processing. It's more likely due to the fact that UDF's tend to complex application-specific algorithms, otherwise they would be included in the standard stock of functions, right? And of course, there is no guarantee that the code in the UDF is GOOD code. MS hasn't yet come up with an error message that goes "You want me to run that? You can't be serious. Click OK to continue, or click Jeez What Was I Thinking to cancel."|||Hi Blindman,

A few months back I had to tune a stp which did a select which returned about 50 columns and about 25000 rows. Of these 50 columns 10 called a udf which only did the following:
CREATE FUNCTION convdate (@.dtMyDate DATETIME)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN CONVERT(VARCHAR(8), @.dtMyDate, 112)
END
After replacing the functions calls with the CONVERT-statement in the select the procedure was about 10 times as fast!

If SQL is still using set-based processing I can't explain why! Got any ideas?

Lex|||i was not talking about using UDFs

I am talking about functions in the client language like VB, PHP, Asp, Java etc|||After replacing the functions calls with the CONVERT-statement in the select the procedure was about 10 times as fast!

If SQL is still using set-based processing I can't explain why! Got any ideas?Sorry. I just don't get the same results. I used these objects:set nocount on
go

CREATE FUNCTION dbo.convdate (@.dtMyDate DATETIME)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN CONVERT(VARCHAR(8), @.dtMyDate, 112)
END

GO

CREATE TABLE TestDates (DateID int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED, DateValue datetime NOT NULL)

Insert into TestDates (DateValue)
select dateadd(Hour, Ones + Tens + Hundreds + Thousands + TenThousands, '2000-01-01')
from (select 0 as Ones
Union select 1
Union select 2
Union select 3
Union select 4
Union select 5
Union select 6
Union select 7
Union select 8
Union select 9) Ones,
(select 0 as Tens
Union select 10
Union select 20
Union select 30
Union select 40
Union select 50
Union select 60
Union select 70
Union select 80
Union select 90) Tens,
(select 0 as Hundreds
Union select 100
Union select 200
Union select 300
Union select 400
Union select 500
Union select 600
Union select 700
Union select 800
Union select 900) Hundreds,
(select 0 as Thousands
Union select 1000
Union select 2000
Union select 3000
Union select 4000
Union select 5000
Union select 6000
Union select 7000
Union select 8000
Union select 9000) Thousands,
(select 0 as TenThousands
Union select 10000
Union select 20000
Union select 30000
Union select 40000
Union select 50000
Union select 60000
Union select 70000
Union select 80000
Union select 90000) TenThousands
order by Ones + Tens + Hundreds + Thousands + TenThousands

Running these statements, I get virtually no difference in execution times:declare @.StartTime datetime
set @.StartTime = getdate()

-- select CONVERT(VARCHAR(8), DateValue, 112)
-- from TestDates

select dbo.convdate(DateValue)
from TestDates

select datediff(ms, @.StartTime, GetDate())
Regardless, you can check the execution plans for each statement and see that they are identical.|||i was not talking about using UDFs

I am talking about functions in the client language like VB, PHP, Asp, Java etc

you got the wrong forum then.|||MS hasn't yet come up with an error message that goes "You want me to run that? You can't be serious. Click OK to continue, or click Jeez What Was I Thinking to cancel."

it appears 2005 does this by making sucky code suck more after the "upgrade".