SQL Server Performance

Create dynamic integer resultset for joining

Discussion in 'Contribute Your SQL Server Scripts' started by Chappy, Mar 6, 2004.

  1. Chappy New Member


    /* ------------------------------------------------
    Function: dbo.GetIntRange
    Author: Paul Chapman (chappi (dot) uk (at) virgin (dot) net)
    Date: 05/03/2004
    Purpose: Dynamically generate a resultset of integers for a given range/increment
    ------------------------------------------------ */
    CREATE FUNCTION dbo.GetIntRange(
    @lowest int,
    @highest int,
    @increment int = 1
    )
    RETURNS @IntRange TABLE (intvalue int)
    AS
    BEGIN
    DECLARE @loopint int
    SELECT @loopint = @lowest
    WHILE (@loopint <= @highest)
    BEGIN
    INSERT INTO @IntRange VALUES (@loopint)
    SELECT @loopint = @loopint + @increment
    END
    RETURN
    END

    Example



    SELECT intValue, "Date Format"=LEFT(CONVERT(varchar, GETDATE(), intValue), 30) from dbo.GetIntRange(100, 114, 1)

    intValue Date Format
    ----------- ------------------------------
    100 Mar 6 2004 12:13PM
    101 03/06/2004
    102 2004.03.06
    103 06/03/2004
    104 06.03.2004
    105 06-03-2004
    106 06 Mar 2004
    107 Mar 06, 2004
    108 12:13:28
    109 Mar 6 2004 12:13:28:233PM
    110 03-06-2004
    111 2004/03/06
    112 20040306
    113 06 Mar 2004 12:13:28:233
    114 12:13:28:233

    (15 row(s) affected)

  2. Chappy New Member

    I have since found that you should use these functions with caution. They are for conveniently generating virtual data which for is impractical/inconvenient to store in a table, dont use them for large joins as it results in a table scan on the function result which can impact performance.

    You can of course use the function to generate the data into a temporary table, then create an index on the temporary table which will perform much better, and still have the benefit of using dynamically generated data

Share This Page