Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

syscolumns names Expand / Collapse
Author
Message
Posted Thursday, November 12, 2009 12:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:34 AM
Points: 313, Visits: 177
Comments posted to this topic are about the item syscolumns names
Post #818045
Posted Wednesday, December 2, 2009 2:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 6, 2014 3:12 AM
Points: 321, Visits: 698
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.
Post #827265
Posted Wednesday, December 2, 2009 2:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:54 AM
Points: 1,174, Visits: 2,656
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.


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #827268
Posted Wednesday, December 2, 2009 3:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:35 AM
Points: 28, Visits: 60
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.
Post #827295
Posted Wednesday, December 2, 2009 4:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:54 AM
Points: 1,174, Visits: 2,656
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.



--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #827309
Posted Wednesday, December 2, 2009 9:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:35 AM
Points: 28, Visits: 60
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.

Post #827598
Posted Wednesday, December 2, 2009 9:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:54 AM
Points: 1,174, Visits: 2,656
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


--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #827627
Posted Thursday, May 13, 2010 8:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:34 AM
Points: 313, Visits: 177
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
Post #921729
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse