SQL Scalar Function called in a View - Is it OK for under 20,000 records? (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 02:39
Joined
Oct 22, 2009
Messages
2,803
In MS Access Queries, many of us are use to creating a public function with parameters and using the function in a Query's field is common.
Using an Access function (including IIF) with SQL Server Linked Tables wreck's all response time.

Just converted a vba function into a Scalar Function on SQL Server. Used it in a SQL View's Field. In the Newbies section of SQLServerCentral, I often get told not to use scalar functions.

Just wondering if others avoid using a Scalar Function in a View for SQL Server?

This is so fast on 10,000 records A fraction of a second. No visible resource impact for SQL Server. Is there any reason to change my ways?
99% of the time, only a single record will be returned. The 1% of usage will be for a Report where all records get run.

Function looks at 4 dates. It does not return the top date - it returns the business situation these dates infer:
My function returns about 10,000 records as part of a View that is made on a View of a 3 Table Outer Join, where each of the 3 Outer Joined tables use a Top 1 value from a view:
Code:
-- Each of 3 Views use this to get latest Date (top 1) value.
SELECT     *
FROM         (SELECT     *, RowID = ROW_NUMBER() OVER (PARTITION BY ID_Wells
                       ORDER BY RE_24DTSub DESC)
FROM         vre_apdfedreturn) AS MyDateOrder
WHERE     RowID = 1
Create a Funtion to use in a View's Column
Code:
CREATE FUNCTION [dbo].[VRE_APD_Trump](
     @SubmitDate DATETIME,
     @ApprovedDate DATETIME,
     @ExpirationDate DATETIME,
     @ReturnedDate DATETIME
    ) 
RETURNS VARCHAR(22) 
AS 
BEGIN 
  DECLARE @FunctionResult as VARCHAR(22); -- Business Condition returned
  --set @timeNow = = SYSDATETIME()  -- gets error! why?
set @FunctionResult = 
 -- Just trying Date 1,2,4 (will evaluate Date 2,3 next)
    CASE WHEN @SubmitDate IS Null AND
              @ReturnedDate IS Null AND
              @ApprovedDate  IS NULL THEN 'No Permit'
   WHEN COALESCE(NULLIF(@ApprovedDate, '19000101'), NULLIF(@ExpirationDate, '19000101'), NULLIF(@ReturnedDate, '19000101')) IS NULL OR
              @SubmitDate > @ApprovedDate AND
              @SubmitDate > @ReturnedDate THEN 'Submit'
         WHEN @ApprovedDate > ISNULL(@SubmitDate, '19000101') AND
              @ApprovedDate > isnull (@ReturnedDate, '19000101') AND
              @ExpirationDate > GETDATE() THEN 'Approved'
         WHEN @ApprovedDate > ISNULL(@SubmitDate, '19000101') AND
     @ExpirationDate < GETDATE() AND 
              @ApprovedDate >ISNULL(@ReturnedDate, '19000101') THEN 'Approved but Expired'
         WHEN @ReturnedDate > ISNULL(@SubmitDate, '19000101') AND
              @ReturnedDate > ISNULL(@ApprovedDate, '19000101')  THEN 'Return'
    END
return @FunctionResult
END;
Then in a SQL View - use the function above: This is very much like creating a VBA function to use in an Access Query's field.
Code:
SELECT     ID_Wells, Well_Name, WellTypeID, ClassificationID, ID_State, R_OverRideRuleCheck, SubmitDate, ApprovedDate, ExpirationDate, ReturnedDate,
                          (SELECT     dbo.VRE_APD_Trump(dbo.VRE_APD_FED_TOP.SubmitDate, dbo.VRE_APD_FED_TOP.ApprovedDate, dbo.VRE_APD_FED_TOP.ExpirationDate, 
                                                   dbo.VRE_APD_FED_TOP.ReturnedDate) AS RE_APD_FedResult) AS APD_FedResult
FROM         dbo.VRE_APD_FED_TOP
Then set up this final View as a Linked Table to Access.
Result: 10,000 records return, sorted, TOP1, Joined, and functioned in a fraction of a second.
 

Users who are viewing this thread

Top Bottom