May 12, 2011 at 7:01 am
Jason-299789 (5/12/2011)
Thanks for the feed back opc, especially on the Distinct vs Group By modification and the tips on XML escaping characters, based on the opening post I would have thought that the Application name wouldnt have escapable characters, though its always possible.
You're welcome. That's the problem with the "XML RAW" approach (and the "XML PATH" approach too BTW). If you cannot be issued a guarantee that the data will never contain those characters that XML escapes then you're leaving yourself open for impacting users and receiving a bug report down the line after data is added or changed in the database. The "XML PATH,TYPE" approach is the only XML approach I know of that handles those characters without escaping them.
The CTE is probably the rout I'd go down in the future, and try and stay clear of the CLR.
The CTE method is fast, reliable and flexible and I cannot argue with the choice. Can I ask why you would "try and stay clear of the CLR"? Initially, when looking at a problem where the SQLCLR could be of service I always ask if there's a way to do it in T-SQL. If T-SQL is an option then I'll almost always go with T-SQL, however for this particular problem the CLR offers comparable performance and a much more intuitive syntax. The CROSS APPLY query in Paul's post took 317 characters (not counting whitespace) to express while the equivalent CLR method can be expressed in 68 characters (also not including whitespace). If the SQLCLR is forbidden in your environment due to prejudice or political reasons that's a whole 'nother conversation entirely...however for this problem I believe the SQLCLR is a viable alternative to T-SQL that adds value.
SELECT group_id ,
dbo.GROUP_CONCAT_S(data, N'ASC')
FROM @data
GROUP BY group_id
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 13, 2011 at 2:53 am
The reason I would stay clear of the CLR root is that a lot of DBA's and outsourcing partners (IBM etc) refuse to accept them as they alledge they pose a security threat to thier databases, especially if the source code is not available.
I personally have no problem with them, its just experience of trying to get them implented by some system providers is just not worth the hassel and hoops you ultimately have to jump through.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
May 13, 2011 at 6:38 am
Yeah...that's what I was hoping not to hear...it's unfortunate and what I would categorize as prejudice. Reasoning on the basis of technical merit with someone who has a prejudice is a lose-lose proposition. At least in this case there is a reliable T-SQL solution however that is not always the case (e.g. regular expressions).
It's funny you mention IBM because IIRC they released a copy of DB2 with a database-resident CLR implementation before Microsoft had released SQL 2005 RTM 😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 13, 2011 at 6:52 am
Tell me about it I've been arguing the a point with IBM over a particular security implementation we need to do for the last couple of weeks, they refuse to allow us to create an SQL user with sp execute rights to a single schema in the database on the grounds that the password could be hacked so opening up access to the data on the server.
However they then go and grant the built in SA sql user db_owner rights to every database.
Go figure.....
_________________________________________________________________________
SSC Guide to Posting and Best Practices
May 13, 2011 at 7:07 am
Jason-299789 (5/13/2011)
However they then go and grant the built in SA sql user db_owner rights to every database.Go figure.....
I figure you should get your money back! Anyone "managing" the permissions of the SA account is not qualified to make SQL Server security decisions :hehe:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 13, 2011 at 7:31 am
opc.three (5/13/2011)
Jason-299789 (5/13/2011)
However they then go and grant the built in SA sql user db_owner rights to every database.Go figure.....
I figure you should get your money back! Anyone "managing" the permissions of the SA account is not qualified to make SQL Server security decisions :hehe:
:hehe::hehe:
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply