syscolumns names

  • Comments posted to this topic are about the item syscolumns names

  • Considering the simplicity and apparent history of working reliably in several places, I found a surprisingly large number of finer points in the submitted code. The asymmetry in trimming was what bothered me the most.

    I removed some fluff such as extra CONTINUE which was exactly as useful here as it is in all the silly WHILE examples in BOL (might be why it had succeeded in sneaking into the code). Also, a set of one members is now handled like any other last element and not as a special case (with its own trimming issue in the original code). These minor issues demonstrate just how dangerous trusted, working, legacy code can be. This looks like it has been used during several years, a workhorse that has seldom let anyone down. And then comes a newbie with his edge cases 🙂

    My version:

    CREATE FUNCTION [dbo].[Udf_TABLECOLUMNLIST]

    (

    @strDataSet VARCHAR(4000)

    )

    RETURNS @Dst TABLE

    (

    x SYSNAME

    )

    AS

    BEGIN

    DECLARE @Idxb INT

    IF @strDataSet IS NULL RETURN -- a rowset having zero rows

    SET @Idxb=CHARINDEX(',',@strDataSet,1)

    WHILE @Idxb<>0

    BEGIN

    INSERT INTO @Dst

    SELECT CAST(LTRIM(RTRIM(SUBSTRING(@strDataSet,1,@Idxb-1))) AS SYSNAME)

    SELECT @strDataSet=LTRIM(RTRIM(SUBSTRING(@strDataSet,@Idxb+1,LEN(@strDataSet)-@Idxb)))

    SET @Idxb=CHARINDEX(',',@strDataSet,1)

    END

    INSERT INTO @Dst

    SELECT CAST(LTRIM(RTRIM(@strDataSet)) AS SYSNAME)

    RETURN

    END

    GO

    SELECT X, LEN(X) AS L FROM dbo.Udf_TABLECOLUMNLIST (' a , b ')

    UNION ALL

    SELECT X, LEN(X) AS L FROM dbo.Udf_TABLECOLUMNLIST (' c ')

    I added the function to my SQL toolbox. Thanks.

  • No need for a while loop to split delimited strings. See Jeff Modens Tally table article, linked to in my sig below, for some great tips on avoiding while loops in this, and many other situations.

  • This is what fascinates me whenever working with SQL - there are always dozens of alternatives 😉

    I analyzed recently some ways to parse a string to a table for performance - with long strings the methods tend to become quite expensive.

    The winner from CPU point of view was the XML method, this goes like this:

    declare @strDataSet as varchar(max);

    declare @delimiter as varchar(10);

    set @strDataSet='A,B,C,D,E';

    set @delimiter =',';

    declare @xml as xml;

    set @xml = cast(('<X>'+replace(@strDataSet,@delimiter ,'</X><X>')+'</X>') as xml);

    select N.value('.', 'sysname') as x from @xml.nodes('X') as T(N);

    Here some performance results for a string with 2361 delimited elements...

    1: XML (see above) - SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 25 ms.

    (2361 row(s) affected)

    2: #Temp (Bernabe's way) - SQL Server Execution Times:

    CPU time = 47 ms, elapsed time = 278 ms.

    (2361 row(s) affected)

    3: CTE - SQL Server Execution Times:

    CPU time = 203 ms, elapsed time = 467 ms.

    Msg 530, Level 16, State 1, Line 19

    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    As you see, CTE has limitations and is rather expensive - but that XML is that much faster than stirng functions in a loop, was a surprise to me...

    Cheers, Richard.

  • Richard,

    Try out Jeffs tally table method, if you haven't already, you should find it's even faster.

    I got the following times on a 9000 element source string.

    ------------ XML Method ------------

    (9000 row(s) affected)

    SQL Server Execution Times:

    CPU time = 235 ms, elapsed time = 228 ms.

    ------------ Tally table method ------------

    (9000 row(s) affected)

    SQL Server Execution Times:

    CPU time = 156 ms, elapsed time = 159 ms.

  • Thanx for your hint, Nigel,

    the Tally Table method was new to me, but I like this - sleek and fast!

    Performance differences are varying - I get slightly different values with every execution - but generally the XML and the Tally Methods are really performant.

    If the tally table must be created first than probably there is not much difference, but...

    If you happen to have your numbers table somewhere in the DB - the tally is indeed even faster than any other solution I tried!

    Thank you again for your valuable contribution!

    Cheers, Richard.

  • Richard,

    No problem at all.

    There's actually a big thread (almost 500 posts) here discussing the various methods of splitting strings that's worth looking at.

    If you have the time, that is 🙂

  • I like the xml way, then I worked on that version

    Here is another way thanks to this forum. It is a generalize version.

    I prepared this version (my original did not used XML)

    By being helped by forums feedback from SQL central

    Forward it to yours peers.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

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

    -- Author: Bernabé Díaz

    -- Create date: 04/08/2010

    -- Description: To return a list from an string

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

    CREATE PROCEDURE Usp_GetTableList

    @strDataSet AS VARCHAR(max)

    ,@delimiter AS VARCHAR(10)=','

    ,@Rt AS VARCHAR(128)='SYSNAME'

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @STR VARCHAR(MAX);

    SET @strDataSet=ISNULL(@strDataSet,'')

    SET @STR=

    '

    DECLARE @xml AS XML;

    SET @xml = CAST((''<X>''+REPLACE('''+@strDataSet+''','''+@delimiter+''' ,''</X><X>'')+''</X>'') AS XML);

    SELECT N.value(''.'', '''+@Rt+''') AS x FROM @xml.nodes(''X'') AS MyTable(N);

    '

    EXEC(@STR)

    END

  • Thanks for the script.

Viewing 9 posts - 1 through 8 (of 8 total)

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