We Have a Date

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  September 28, 2017 at 9:43 pm

  #1961506

  Jason A. Long - Thursday, September 28, 2017 6:05 PM

  Alan.B - Thursday, September 28, 2017 5:07 PM

  The Data Tuning Adviser is going to recommend columnstore indexes for me? October 2nd can't come soon enough! </sarcasm>

  It's cool to finally see some new T-SQL commands. TRIM and CONCAT_WS are not game changers but  I was excited about STRING_AGG and TRANSLATE. Unfortunately, as was the case with string_split, STRING_AGG and TRANSLATE were poorly implemented IMHO. 

  I've been using 2017 SSMS for a while and I have seen some cool changes to the execution plans.

  Really?... STRING_AGG (or at least the functionality) has been a wish list for quite awhile now. I hate XML and I always feel a bit "unclean" every time I use FOR XML PATH('').
  Is it not as fast as XML (or JSON)?

  I don't like XML concatenation at all and was very happy to hear about STRING_AGG; it was actually the first thing I played around with after installing 2017.
  It's a is a HUGE improvement, don't get me wrong. I work with Redshift (postgresql) and it's STRING_AGG has DISTINCT. In SQL server you
  have to do this:

  SELECT csv = STRING_AGG(token,',')
FROM
( SELECT DISTINCT token
FROM dbo.ngrams8k('aabbcc', 1)
) ng(token);

  ..which all of a sudden is not much prettier than:

  SELECT csv = STUFF(
( SELECT DISTINCT ','+token
FROM dbo.ngrams8k('aabbcc', 1)
FOR XML PATH('')),1,1,'');
  
  With STRING_AGG I can guarantee I get the rows in the order I want using WITHIN GROUP: 
  SELECT csv = STRING_AGG(token,',') WITHIN GROUP (ORDER BY position)
FROM dbo.ngrams8k('abc', 1);

  Without STRING_AGG DISTINCT, however, I can't figure out how I would return a distinct concatenated set in the order I want? E.g. how do I get this to work?
  SELECT csv = STRING_AGG(token,',') WITHIN GROUP (ORDER BY position)
FROM
( SELECT DISTINCT token
FROM dbo.ngrams8k('aabbcc', 1)
) ng(token);

  Again - I'm very happy to see it, "poorly implemented" was not a good choice of words for STRING_AGG. 

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Jason A. Long

  SSC-Insane

  Points: 23789

  More actions

  September 28, 2017 at 10:41 pm

  #1961510

  Alan.B - Thursday, September 28, 2017 9:43 PM

  Jason A. Long - Thursday, September 28, 2017 6:05 PM

  Alan.B - Thursday, September 28, 2017 5:07 PM

  The Data Tuning Adviser is going to recommend columnstore indexes for me? October 2nd can't come soon enough! </sarcasm>

  It's cool to finally see some new T-SQL commands. TRIM and CONCAT_WS are not game changers but  I was excited about STRING_AGG and TRANSLATE. Unfortunately, as was the case with string_split, STRING_AGG and TRANSLATE were poorly implemented IMHO. 

  I've been using 2017 SSMS for a while and I have seen some cool changes to the execution plans.

  Really?... STRING_AGG (or at least the functionality) has been a wish list for quite awhile now. I hate XML and I always feel a bit "unclean" every time I use FOR XML PATH('').
  Is it not as fast as XML (or JSON)?

  I don't like XML concatenation at all and was very happy to hear about STRING_AGG; it was actually the first thing I played around with after installing 2017.
  It's a is a HUGE improvement, don't get me wrong. I work with Redshift (postgresql) and it's STRING_AGG has DISTINCT. In SQL server you
  have to do this:

  SELECT csv = STRING_AGG(token,',')
FROM
( SELECT DISTINCT token
FROM dbo.ngrams8k('aabbcc', 1)
) ng(token);

  ..which all of a sudden is not much prettier than:

  SELECT csv = STUFF(
( SELECT DISTINCT ','+token
FROM dbo.ngrams8k('aabbcc', 1)
FOR XML PATH('')),1,1,'');
  
  With STRING_AGG I can guarantee I get the rows in the order I want using WITHIN GROUP: 
  SELECT csv = STRING_AGG(token,',') WITHIN GROUP (ORDER BY position)
FROM dbo.ngrams8k('abc', 1);

  Without STRING_AGG DISTINCT, however, I can't figure out how I would return a distinct concatenated set in the order I want? E.g. how do I get this to work?
  SELECT csv = STRING_AGG(token,',') WITHIN GROUP (ORDER BY position)
FROM
( SELECT DISTINCT token
FROM dbo.ngrams8k('aabbcc', 1)
) ng(token);

  Again - I'm very happy to see it, "poorly implemented" was not a good choice of words for STRING_AGG. 

  Nah... It's as you said. Had they simply put in a wee bit more thought into usability, adding a DISTINCT option would have been a no brainier... Same thing with the missing RN on STRING_SPLIT.
  And it's not as if it hasn't been on anyone's radar. Itzik Ben-Gan has been begging for DISTINCT in the windowed aggregates for years now... If they won't listen to him, I'm not sure we have much hope... :crying: