Home Forums SQL Server 2008 T-SQL (SS2K8) How to use values keyword as parameter of a function RE: How to use values keyword as parameter of a function

  • Jeff Moden (11/29/2012)


    capnhector (11/29/2012)


    EDIT: This is also for me to play with a couple of things and get more experience with them. so it may not be fast or work in your situation but does use some things i have been studying.

    No.... you're absolutely on the correct path. The MIN=MAX method the others used is twice as slow as the original function. You can make it a bit faster still by turning it into an iTVF instead of a scalar function. And lose the join. If you rework the GenericTable TYPE just a bit, you won't need it. Give it a shot. You've got this, Cap'n!

    I cant believe i forgot to post the iTVF and new GenericTable rework as well. the things i was playing with was the cross apply values to unpivot the data.

    The join was just to show that it came out with the right results. having the Generic Table type with just ID and Item allows any number of items to be passed in by just changing the cross apply unpivot.

    CREATE TYPE GenericTable AS TABLE (ID INT, D sql_variant)

    GO

    CREATE FUNCTION AreEqual (@t as dbo.GenericTable readonly)

    RETURNS TABLE WITH SCHEMABINDING

    AS

    RETURN SELECT ID, CASE WHEN COUNT(DISTINCT D) = 1 THEN 1 ELSE 0 END AS 'TF'

    FROM @t

    GROUP BY ID

    GO

    It is slow as hell for any thing larger than about 50,000 records on my system but chuggs them out just fine.

    Here is the test bed i used

    IF OBJECT_ID('tempdb..#Tmp') IS NOT NULL DROP TABLE #Tmp

    --The temp table makes things a little easier

    SELECT TOP (10000) --10 thousand is still nice and fast can change it later

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) I,

    ABS(CHECKSUM(NEWID())) % 2 D1, --Gives us a small sub set so we will get more positive returns than a random seed of higher values

    ABS(CHECKSUM(NEWID())) % 2 D2,

    ABS(CHECKSUM(NEWID())) % 2 D3

    INTO #Tmp

    FROM sys.all_columns a, sys.all_columns b

    DECLARE @T AS GenericTable

    --Normalize the data so it plays nicely with our iTVF

    INSERT INTO @T

    SELECT I, Value

    FROM #Tmp

    CROSS APPLY (VALUES (D1),(D2),(D3))X(Value)

    SELECT *

    FROM AreEqual (@T) a

    INNER JOIN #Tmp b

    ON a.ID = b.I


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]