SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Bernabe Diaz / syscolumns names / Latest Posts

RE: syscolumns names

diaz.bernabe — Thu, 13 May 2010 20:47:17 GMT

I like the xml way, then I worked on that versionHere 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 centralForward it to yours peers. SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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'ASBEGIN 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

RE: syscolumns names

nigel. — Wed, 02 Dec 2009 09:33:07 GMT

Richard,No problem at all.There's actually a big thread (almost 500 posts) [url=http://www.sqlservercentral.com/Forums/Topic695508-338-1.aspx][u]here[/u][/url] discussing the various methods of splitting strings that's worth looking at.If you have the time, that is :-)

RE: syscolumns names

richard.jereb — Wed, 02 Dec 2009 09:11:24 GMT

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.

RE: syscolumns names

nigel. — Wed, 02 Dec 2009 04:15:25 GMT

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.[code="plain"] ------------ 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.[/code]

RE: syscolumns names

richard.jereb — Wed, 02 Dec 2009 03:32:28 GMT

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:[code="sql"]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);[/code]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.

RE: syscolumns names

nigel. — Wed, 02 Dec 2009 02:27:10 GMT

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.

RE: syscolumns names

Arto Ahlstedt — Wed, 02 Dec 2009 02:22:38 GMT

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:[code="sql"]CREATE FUNCTION [dbo].[Udf_TABLECOLUMNLIST]( @strDataSet VARCHAR(4000)) RETURNS @DST TABLE( x SYSNAME) ASBEGINDECLARE @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 ENDGOSELECT X, LEN(X) AS L FROM dbo.Udf_TABLECOLUMNLIST (' a , b ')UNION ALLSELECT X, LEN(X) AS L FROM dbo.Udf_TABLECOLUMNLIST (' c ')[/code]I added the function to my SQL toolbox. Thanks.

syscolumns names

diaz.bernabe — Thu, 12 Nov 2009 12:28:11 GMT

Comments posted to this topic are about the item [B]syscolumns names[/B]