May 31, 2022 at 1:47 pm
You should get rid of the local variable as it's not needed and causes overhead. Also, make sure that ANSI_NULLS and QUOTED_IDENTIFIER are set properly when the function is created.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION [dbo].[F_ISWOSTATUSWAITPOSSIBLE] (
@WO_Status char(2)
)
RETURNS bit
AS
BEGIN
RETURN (
CASE WHEN @WO_Status in ('30', '40', '50', '60', '65', '70') THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END
)
END
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 31, 2022 at 1:58 pm
Indeed, but best is to just avoid the SVF
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 31, 2022 at 2:06 pm
Sorry... not enough coffee yet. Misread some code and removed the post.
And I totally agree with Johan... avoid Scalar UDFs at all costs. They even prevent parallelism if you do a select from a table if you have one in a computed column even if you don't use the computed column. Brent Ozar wrote about that and I've verified his findings.
--Jeff Moden
May 31, 2022 at 2:17 pm
Something like this?
WITH cteAggregated(CustomerID, ItemID, Yak)
AS (
SELECT CustomerID,
ItemID,
MIN(CAST(MyLevelHere AS VARBINARY(1)) + CAST(MyPriceHere AS VARBINARY(MAX)) AS Yak
FROM dbo.MyTableNameHere
GROUP BY CustomerID,
ItemID
)
SELECT CustomerID,
ItemID,
CAST(SUBSTRING(Yak, 1, 1) AS VARCHAR(1)) AS MyLevelHere,
CAST(SUBSTRING(Yak, 2, LEN(Yak) - 1) AS DECIMAL(19, 4)) AS MyPriceHere
FROM cteAggregated;
It's hard to say exactly without knowing your DDL.
N 56°04'39.16"
E 12°55'05.25"
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply