Hi,
Just want to validate that, contrary to 2000, 2005 will tolerate the use of SELECT statements within functions.
Thanks
Re: Differences between 2000 and 2005
Arnie Rowland
That's not true. BOTH SQL 2000 and 2005 will allow the proper use of SELECT statements inside the function.
However, neither will allow a SELECT statement to be used to return data to the client outside of the defined function data return (either scalar or TVF.)
Re: Differences between 2000 and 2005
fleo
Thanks
Are you aware of any difference between the 2 versions (regarding functions)
Re: Differences between 2000 and 2005
Arnie Rowland
Yes, there is a lot of new functionality that can be employed in User Defined Functions. New T-SQL keywords, xml datatypes, xquery, clr functions, etc. The list of new capabilities is very long.
Can you be more specific about your needs
Re: Differences between 2000 and 2005
fleo
Arnie Rowland wrote:
Yes, there is a lot of new functionality that can be employed in User Defined Functions. New T-SQL keywords, xml datatypes, xquery, clr functions, etc. The list of new capabilities is very long.
Can you be more specific about your needs
Sorry...
I meant besides new functionnalities in SQL Server 2005 (which cannot be used in 2000).
We found the problem...
We were trying to implement a function to bypass the real datetime. So instead of using getdate(), we would use fn_getdate().
fn_getdate() returns either a date from a table or getdate() if no value is found.
In SQL 2000 you cannot use getdate() in a function. That was the source of the error.
Thanks
Re: Differences between 2000 and 2005
Tom Phillips
This function works fine in 2005
Post your code. What exactly is the error you are getting
This function works fine in 2005
Code Snippet
IF OBJECT_ID (N'dbo.fn_getdate', N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_getdate;
GO
CREATE FUNCTION dbo.fn_getdate ()
RETURNS datetime
AS
BEGIN
DECLARE @newdate datetime
SELECT @newdate= CASE WHEN dt.date IS NULL THEN GETDATE() ELSE dt.date END FROM TestTable dt
RETURN(@newdate)
END;
GO
SELECT dbo.fn_getdate()
Post your code. What exactly is the error you are getting
Re: Differences between 2000 and 2005
Tom Phillips
Sorry, I said 2000 and 2005 and that is incorrect. The code is 2005 specific.
Sorry, I said 2000 and 2005 and that is incorrect. The code is 2005 specific.
Re: Differences between 2000 and 2005
Tom Phillips
This is the best workaround I could think of for 2000:
This is the best workaround I could think of for 2000:
Code Snippet
IF OBJECT_ID (N'dbo.fn_getdate', N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_getdate
GO
CREATE FUNCTION dbo.fn_getdate(@curdate AS DATETIME)
RETURNS datetime
AS
BEGIN
DECLARE @newdate datetime
SELECT @newdate=NULL --- Insert code here for table
IF @newdate IS NULL
SET @newdate = @curdate
RETURN(@newdate)
END
GO
SELECT dbo.fn_getdate(GETDATE())
Re: Differences between 2000 and 2005
fleo
Hi Tom,
Nice workaround... Thanks
I've been through many articles about differences between 8.0 and 9.0 but none mention this one. Do you know the reason behind and are you aware of similar differences
Thanks again
Re: Differences between 2000 and 2005
Tom Phillips
I was not able to find any specific reference to "fixing" this issue. But I knew that worked because I used it a few months ago in 2005, unknowing it did not work in 2000 until I tried it.
I generally stay away from functions because of performance issues. UDF are very costly in execution time. If you can do what you want another way, without a UDF, I would.
I was not able to find any specific reference to "fixing" this issue. But I knew that worked because I used it a few months ago in 2005, unknowing it did not work in 2000 until I tried it.
I generally stay away from functions because of performance issues. UDF are very costly in execution time. If you can do what you want another way, without a UDF, I would.
Re: Differences between 2000 and 2005
fleo
I generally stay away from functions because of performance issues. UDF are very costly in execution time. If you can do what you want another way, without a UDF, I would.
What do you mean by "very costly"
I ran benchmarks and in my case the results show UDFs are slower by about 10%.
Re: Differences between 2000 and 2005
Tom Phillips
In your case it might only be 10% slower, for this particular function.
But, if you do something like:
SELECT *
FROM Table
WHERE udf(table.abc)
and Table has 100,000,000,000 rows, it executes the udf for every single row in a table scan mode to find out if it matches the where clause.
In your case it might only be 10% slower, for this particular function.
But, if you do something like:
SELECT *
FROM Table
WHERE udf(table.abc)
and Table has 100,000,000,000 rows, it executes the udf for every single row in a table scan mode to find out if it matches the where clause.