March 2, 2011 at 1:27 pm
Anyone have an example(s) where passed parameters are used in a query, accessing a table, and returns a scalar?
My query below works (Value, Tagname, DateTime are existing table headers). Unable to assign a scalar from query result.
GO
SELECT ROUND(ROUND(MAX(Value),1)-ROUND(MIN(Value),1),1)
FROM History
WHERETagName = 'Machine 1 Power'
AND DateTime >= '2010-12-23 0:00'
AND DateTime <= '2010-12-23 23:59'
GO
March 2, 2011 at 2:12 pm
Not exactly what you mean. What are you trying to do?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 2, 2011 at 2:26 pm
I don't understand if you're trying to create a scalar UDF...
CREATE FUNCTION getSomeValue
(
@TagName varchar(128),
@StartDate datetime,
@EndDate datetime
)
RETURNS float
AS BEGIN
RETURN (SELECT ROUND(ROUND(MAX(Value), 1) - ROUND(MIN(Value), 1), 1)
FROM History
WHERE TagName = @TagName
AND [DateTime] >= @StartDate
AND [DateTime] <= @EndDate
)
END
... or trying to assign the result of this query to a variable:
DECLARE @someValue float
SELECT @someValue = ROUND(ROUND(MAX(Value),1)-ROUND(MIN(Value),1),1)
FROM History
WHERETagName = 'Machine 1 Power'
AND DateTime >= '2010-12-23 0:00'
AND DateTime <= '2010-12-23 23:59'
Can you clarify, please?
-- Gianluca Sartori
March 3, 2011 at 9:25 am
Received in PM:
tonymartini777 (3/2/2011)
Sir,Your response was spot on. Sorry for the confusion. Here goes the long version.
I want to create a scalar UDF where I pass the said 'tagname, starttime, endtime.' Those parameters would be used to query a table. The query result would be a max scalar 'value' returned from the function. I was unsuccessful in getting the syntax correct and didn't have a good resource for SQL-T.
Thanks
Great, glad I could help.
Be sure to post your replies here, so that anyone can find it beneficial.
-- Gianluca Sartori
March 4, 2011 at 12:32 pm
Tony,
Please be advised that while this type of scalar UDF (accessing a table based on parameters) has its place in some applications, it can lead to very bad performance that is invisible in the query plan.
The UDF has to access the table once for each row in the main query and the optimizer won't be able to get a plan that is set based for the UDF.
If the main query returns more than a few rows it can be a real pig as a performer.
Todd Fifield
March 9, 2011 at 7:15 am
tfifield (3/4/2011)
Tony,Please be advised that while this type of scalar UDF (accessing a table based on parameters) has its place in some applications, it can lead to very bad performance that is invisible in the query plan.
The UDF has to access the table once for each row in the main query and the optimizer won't be able to get a plan that is set based for the UDF.
If the main query returns more than a few rows it can be a real pig as a performer.
Todd Fifield
An alternative will be to give the definition of the scalar UDF inline in the main query, Todd?
- arjun
https://sqlroadie.com/
March 9, 2011 at 8:32 am
Arjun Sivadasan (3/9/2011)
tfifield (3/4/2011)
Tony,Please be advised that while this type of scalar UDF (accessing a table based on parameters) has its place in some applications, it can lead to very bad performance that is invisible in the query plan.
The UDF has to access the table once for each row in the main query and the optimizer won't be able to get a plan that is set based for the UDF.
If the main query returns more than a few rows it can be a real pig as a performer.
Todd Fifield
An alternative will be to give the definition of the scalar UDF inline in the main query, Todd?
- arjun
... or coding it as an ITVF, which is quite the same from a performance standpoint, but allows better code re-use.
-- Gianluca Sartori
March 9, 2011 at 1:16 pm
Gianluca Sartori (3/9/2011)
Arjun Sivadasan (3/9/2011)
tfifield (3/4/2011)
Tony,Please be advised that while this type of scalar UDF (accessing a table based on parameters) has its place in some applications, it can lead to very bad performance that is invisible in the query plan.
The UDF has to access the table once for each row in the main query and the optimizer won't be able to get a plan that is set based for the UDF.
If the main query returns more than a few rows it can be a real pig as a performer.
Todd Fifield
An alternative will be to give the definition of the scalar UDF inline in the main query, Todd?
- arjun
... or coding it as an ITVF, which is quite the same from a performance standpoint, but allows better code re-use.
Arjun,
Putting the code from the scalar function in-line in the main query will certainly give the optimizer a chance to fit the related tables into the query plan and optimize for the data set.
I've found that ITVF's, properly coded, are way faster than either scalar functions or multi-statement table valued functions - especially if Schema Bound.
It really depends on what the scalar function is doing. If it's just some sort of string manipulation, then the only thing you lose really is parallelism. If it's accessing another table, or even the table from the main query, then it's a pig performance wise.
Todd Fifield
March 9, 2011 at 8:41 pm
Thanks Todd and Gianluca. I wanted the OP to know his options. Yes, ITVF, when it does the job, is the best option.
- arjun
https://sqlroadie.com/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply