• Gatekeeper

    SSCommitted

    Points: 1583

    We've found good use of the following tally table and UDF. It handles empty elements and works fast for us. We've done large and small sets against it.

    CREATE TABLE dbo.Tally

    (N INT)

    -- Default Data

    -- Taken from the following website:

    -- http://www.sqlservercentral.com/articles/TSQL/62867/

    --=============================================================================

    -- Create and populate a Tally table

    --=============================================================================

    --===== Conditionally drop

    IF OBJECT_ID('dbo.Tally') IS NOT NULL

    DROP TABLE dbo.Tally

    --===== Create and populate the Tally table on the fly

    SELECT TOP 3000000 --equates to more than 30 years of dates

    IDENTITY(INT,1,1) AS N

    INTO dbo.Tally

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

    ALTER TABLE dbo.Tally

    ADD CONSTRAINT PK_Tally_N

    PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

    --===== Let the public use it

    GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

    GO

    CREATE FUNCTION udf_StrList2Table (

    @List NVARCHAR(MAX) = N'',

    @Delimiter NCHAR(1) = N','

    )

    RETURNS TABLE

    AS

    RETURN

    SELECT SUBSTRING( @Delimiter + @List + @Delimiter, n + 1,

    CHARINDEX( @Delimiter, @Delimiter + @List + @Delimiter, n + 1 ) - n - 1 ) AS Value,

    ROW_NUMBER() OVER ( ORDER BY n ) AS ListPos

    FROM Tally

    WHERE SUBSTRING( @Delimiter + @List + @Delimiter, n, 1 ) = @Delimiter

    AND n < CAST(LEN( @Delimiter + @List + @Delimiter ) as int)

    /* Anything is possible but is it worth it? */