Post reply

Using SP in UDF

  • October 13, 2016 at 10:25 am

    #1906400

    Alaster07 (10/13/2016)

    @Sergiy

    I tried your approach, and it kind of mostly works. The issue I am running into is that the "early" times will all be at the front of the result set and all of the "late" times will be at the end of the result set.

    dbo.GenerateRandomDateTime('2016-01-01', 287, 43200)

    Here's the result set on ~10,000 rows. http://pastebin.com/8eVJFHWt

    As you can see, the beginning of the year has the lower times and the end of the year has the higher times. I assume this is because both the date and time parts are using the same seed to "randomize"?

    Here's an "improved" version from the function I posted before that allows to return just date or just time. As mentioned several times, an inline table-valued function will perform better (sometimes over a hundred times better) than a scalar function.

    ALTER FUNCTION GenerateRandomDateTime

    (

    @MinimumDate DATE,

    @MaximumDayDifference INT,

    @MaximumSecondsFromMidnight INT

    ) RETURNS TABLE

    AS

    RETURN

    SELECT DATEADD( SS,

    ISNULL(ABS( CHECKSUM(v1._NewID)) % x.MaxSeconds, 0),

    DATEADD(DAY, ISNULL(ABS(CHECKSUM(v2._NewID) % DayDifference), 0), MinDate)

    ) RandomDate

    FROM vNEWID v1, vNEWID v2,

    (VALUES(NULLIF(ISNULL(@MaximumSecondsFromMidnight, 86400), 0),

    NULLIF(ISNULL(@MaximumDayDifference, 365), 0),

    ISNULL(CAST( @MinimumDate AS datetime), DATEADD(YEAR, DATEDIFF( YEAR, 0, GETDATE()), 0))))x(MaxSeconds, DayDifference, MinDate);

    GO

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 14, 2016 at 11:52 am

    #1906648

    I understand that an iTVF is better; however, I have no experience using them. I have read about them and attempted to implement it, but it was not working for me.

  • October 14, 2016 at 1:50 pm

    #1906681

    Alaster07 (10/14/2016)

    I understand that an iTVF is better; however, I have no experience using them. I have read about them and attempted to implement it, but it was not working for me.

    No problem...have a play with this:

    -- Up to a million rows with two columns, n is bigint sequence from 1 to ...

    -- and r is a semirandom integer ABS(CHECKSUM(NEWID()))

    CREATE VIEW [dbo].[vw_RandomStuff]

    WITH SCHEMABINDING AS

    WITH

    E1 AS (SELECT n = 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d (n)),

    E2 AS (SELECT n = 1 FROM E1 a CROSS JOIN E1 b),

    E6 AS (SELECT n = 1 FROM E2 a CROSS JOIN E2 b CROSS JOIN E2 c),

    _Tally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E6)

    SELECT n, r = ABS(CHECKSUM(NEWID()))

    FROM _Tally

    GO

    -- Returns @NumberOfRows rows of data between

    -- @StartDate and @StartDate + @DateSpanDays days

    -- Outputting sequence n to ...

    -- a random date,

    -- the date component of the random date,

    -- the time component of the random date.

    -- Fast enough, returns a million rows back to client in about 4s.

    CREATE FUNCTION [dbo].[itvf_RandomDatetime] (

    @StartDate DATE,

    @DateSpanDays INT,

    @NumberOfRows INT)

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    SELECT

    rs.n,

    y.RandomDatetime,

    RandomDate = CAST(x.RandomDate AS DATE),

    RandomTime = CAST(y.RandomDatetime AS TIME(3))

    FROM dbo.vw_RandomStuff rs

    CROSS APPLY (SELECT RandomDate = DATEADD(DAY,r%@DateSpanDays,CAST(@StartDate AS DATETIME))) x

    CROSS APPLY (SELECT RandomDateTime = DATEADD(millisecond,r%86400000,x.RandomDate)) y

    WHERE rs.n <= @NumberOfRows

    GO

    -- Usage

    SELECT * FROM [dbo].[itvf_RandomDatetime] ('20161003', 10, 1000000)

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • October 17, 2016 at 10:36 am

    #1906930

    Thank you all for the input and assistance.

  • October 17, 2016 at 11:12 am

    #1906940

    Alaster07 (10/14/2016)

    I understand that an iTVF is better; however, I have no experience using them. I have read about them and attempted to implement it, but it was not working for me.

    It's virtually the same as writing a view but with parameters.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply