What's new in SQL Server 2017? For SSAS Tabular, lots, it transpires.
Part three of a six-part series of blogs

This blog summarises the main new features of SQL Server for non-administrators (that is, most of us). Most parts of SQL Server get minor changes at best, but SSAS Tabular 2017 gets a host of major improvements.

  1. What's new in SQL Server 2017 (differences versus 2016)
  2. Changes to SQL Server 2017 installation
  3. What's new in the T-SQL language in SQL Server 2017 (this blog)
  4. What's new in Integration Services 2017 (SSIS 2017)
  5. What's new in Reporting Services 2017 (SSRS 2017)
  6. What's new in Analysis Services 2017 (SSAS 2017)

Posted by Andy Brown on 23 January 2018

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

What's new in the T-SQL language in SQL Server 2017?

Not much!  I've listed the new functions under separate headings below.

At last: the TRIM function!

There is one new function in SQL Server 2017 which is way, way overdue - TRIM!

The TRIM function

At long, long last - if you want to strip trailing and leading blanks from a string of text, you no longer have to combine RTRIM and LTRIM. SQL Server enters the 21st century!

 

Using STRING_AGG to join column values together

The string_agg function makes it much easier to combine values of a column from different rows:

string_agg function

The function even supports Intellisense when choosing the column name whose values you want to combine.

So if you have a table like this called tblOwl:

Table of owls

This table contains the names of 5 owls.

 

Then you could run this query:

-- combine all values of first 3 owls

SELECT

string_agg(OwlName,',') AS Owls

FROM

tblOwl

WHERE

OwlId <= 3

This would show the first 3 column values, joined together with a comma:

The query result

The result of running the above query.

 

You used to be able to do this by messing about with XML file paths - I for one won't miss this fudge!

Using CONCAT_WS to concatenate with separators

The CONCAT_WS function allows you to join bits of text together, and ignores nulls:

-- show name of Wise Owl

SELECT CONCAT_WS(

'_',

'Wise',

Null,

'Owl',

Null,

'Training'

)

So the query above would show this output:

Joining text together

The null values are ignored.

 

Use TRANSLATE to replace one string pattern with another

This function is best explained by an example:

-- change brackets and extension letter

-- in a phone number

SELECT

TRANSLATE(

'+44 (1457) 858877 x 2199',

'() x',

'[] X'

)

This query will display:

The query results

The query makes 3 replacements, as shown below.

 

The query replaces ( with [, ) with ] and x with X, avoiding the need to use a series of REPLACE functions.

The fact that the search string and replacement string have to be the same length makes this function of limited use.

This blog has 0 threads Add post