The +- is roughly 10% in either direction so I am comfortable saying they are "comparable" for general use. In some test cases the XML performs best, in others the SQL CLR performs best. In my testing (test scripts are included in the CodePlex download) the SQLCLR object is on par with the XML TYPE method shown in the article in terms of performance.
Here is the equivalent query using my SQLCLR: That is where the penalty I am referring too is introduced. If the dataset you're aggregating on is not unique and you do not need to apply DISTINCT then SQL will not need to sort or de-duplicate the key column to aggregate the results, whereas the GROUP BY always will (unless the optimizer can ignore it due to the presence of a unique index). I am now on the same page as far as which DISTINCT you were referring too. Is this based on your results from implementing it using CLR? I'm not sure how you arrive at the conclusion that the XML version would outperform GROUP_CONCAT if the latter were implemented in MS's product. This produces the same query plan as the CTE version, and has the same IO profile, so it is incorrect to assert some penalty for using GROUP BY. The query could be rewritten like this, and it's the same query from the optimizer's perspective: My point is that the solution presented in the article is the equivalent to a solution using GROUP BY, so saying that there's no GROUP BY clause is only accurate when talking about the syntax. Now, from a syntax point of view, when the outer query must be distinct, I like the GROUP_CONCAT() method which is why I wrote GROUP_CONCAT() for SQL Server by leveraging the SQLCLR but it is not the best choice for all scenarios.ĭISTINCT Value ORDER BY Value) AS 'CommaList' If GROUP_CONCAT() existed on SQL Server the XML technique from the article would almost certainly outperform it so while it may seem like a fair comparison from a query results point of view it's really apples and oranges under the hood. All I am pointing out is that there is a difference in implementation, semantical maybe, but there is a penalty for using GROUP BY when it is not needed when compared to using a correlated subquery as Wayne presents it. I am not arguing about the likeness of the results however.
If the outer query then yes, I agree, an aggregate may be a better option. SELECT AccountNumber, GROUP_CONCAT( DISTINCT Value ORDER BY Value) AS 'CommaList' Not sure what you're getting at when you talk about DISTINCT, whether you mean in the outer query or the correlated subquery, but if the subquery you would need to provide DISTINCT when using GROUP_CONCAT() as well, if duplicate values were a concern: It produces the same output as the T-SQL solution. SELECT AccountNumber, GROUP_CONCAT(Value ORDER BY Value) AS 'CommaList' Here's the MySQL solution to the comma-separated list problem: Take a look at the CTE from the article, and consider the effect of the "DISTINCT" keyword. Notice that the query in the article is not an aggregate, i.e.
MySQL's GROUP_CONCAT() function implements a semantically different piece of functionality than what Wayne has demonstrated in the article. It's something to be aware of in case you ever need to migrate from a MySQL db. In T-SQL, if the separator is a NULL value, the the string values are still concatenated, but without a separator.Not that I advocate switching to a "toy" RDBMS, but MySQL does have the GROUP_CONCAT operator which makes this type of thing a little more elegant. This is one of the differences between MySQL and T-SQL (SQL Server, Azure). If the separator itself is a NULL value, the concatenation operation will return NULL.Įxample: SELECT CONCAT_WS(NULL,'Auckland', NULL, 'New Zealand') AS Location If any of the arguments is a NULL value, MySQL will skip that value and its separator, but it will still process the others.Įxample: SELECT CONCAT_WS(', ','Auckland', NULL, 'New Zealand') AS Location Here’s an example of retrieving data from a database, and combining two columns into one, separated by a comma: SELECT CONCAT_WS(', ', city.Name, country.Name ) AS Location SELECT CONCAT_WS(' - ','Paris', 'France') AS Location Here’s the same example as the previous one, except this one uses a different separator. There’s nothing to say that the separator must be a comma.
Here’s an example: SELECT CONCAT_WS(',','Sydney', 'Australia') AS Location Īnd you can add a space in there if you want: SELECT CONCAT_WS(', ','Sydney', 'Australia') AS Location If you just use the CONCAT() function, you’d have no separator (unless you explicitly added a separator as an argument between each string argument).Ī common usage of the CONCAT_WS() function is to create a comma-delimited list. In MySQL, the CONCAT_WS() function allows you to add a separator to concatenated strings. Here is the query: SELECT projects.projID, projects.createdBy, projects.createdOn, projects.projName, projects.projDesc, ( SELECT CONCAT (users.fname,' ',users.lname) FROM users,projassignment WHERE erName erName ) AS assignedTo FROM projects,admin,users LEFT JOIN projassignment ON projects.projID projassignment.
0 kommentar(er)
