Posts Tagged ‘SQL Server 2018’

New built-in function CONCAT_WS() in SQL Server vNext (2018)

January 20, 2017 1 comment

In my previous posts I discussed new Functions introduced in SQL Server vNext (or 2018), like STRING_AGG(), TRIM(), TRANSLATE().

Here in this post I’ll discuss about one more new function i.e. CONCAT_WS(), here “_WS” means “With Separator”.

This is very similar to the existing CONCAT() function introduced back in SQL Server 2012, which concatenates a variable number of arguments or string values.

The difference is the new function CONCAT_WS() accepts a delimiter specified as the 1st argument, and thus there is no need to repeat the delimiter after very String value like in CONCAT() function.

Also the new CONCAT_WS() function takes care of NULL values and do not repeat the delimiter, which you can see in 2nd example below.


CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )


–> Example #1:

With CONCAT_WS() we will use the delimiter just once and it concatenates the names separated by ‘-‘, and do not repeat the hyphen where the middle name is NULL.

USE [AdventureWorks2014]

	CONCAT_WS(' - ', FirstName, MiddleName, LastName) as FullName, 
	FirstName, MiddleName, LastName
FROM [Person].[Person]


–> Example #2:

With CONCAT() the delimiter needs to be used after every argument, it concatenates the names separated by ‘-‘, do repeats the hyphen where the middle name is NULL.

	CONCAT(FirstName, ' - ', MiddleName, ' - ', LastName) as FullName, 
	FirstName, MiddleName, LastName
FROM [Person].[Person]



New built-in function TRANSLATE() in SQL Server vNext (2018)

December 29, 2016 2 comments

Microsoft looks very serious this time to move people from other databases to SQL Server. As with SQL Server 2016 & vNext (2018) you can see lot of Built-in function added, which were present in other databases from long back, will ease database development in SQL Server.

One of this function is TRANSLATE() function, which can be used like a REPLACE() function, and would avoid using REPLACE() function multiple times in a query.


TRANSLATE ( inputString, characters, translations)

Note: characters and translations params should have same length.

–> Consider this example I’ve taken from MSDN:

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');


Input Output
2*[3+4]/{7-2} 2*(3+4)/(7-2)


–> If you had to do same with REPLACE() function then you would end up writing multiple & nested REPLACE() function, like:

				REPLACE('2*[3+4]/{7-2}', '[', '('), 
			']', ')'), 
		'{', '('), 
	'}', ')');


After working with this new feature it reminds me of IIF vs CASE statement. The IIF() function also works as a shortcut of CASE statement and cuts lot of clutter and gives you clean code.

Hope you find this small utility very handy while developing complex queries, will post more scenarios if I came across going forward, thanks !!!

New built-in function TRIM() in SQL Server vNext (2018)

December 26, 2016 1 comment

If you are thinking the new TRIM() function in SQL Server vNext is just a combination of LTRIM() & RTRIM() functions, then you are wrong :). It’s more than that and we will check it today !

– LTRIM() function is used to truncate all leading blanks, or white-spaces from the left side of the string.

– RTRIM() function is used to truncate all trailing blanks, or white-spaces from the right side of the string.

–> Now, with teh new TRIM() function you can do both, but more than that.

Usage #1: TRIM() function will truncate all leading & trailing blanks from a String:

	TRIM ('  Manoj Pandey     ') as col1,
	LTRIM(RTRIM('  Manoj Pandey     ')) as col2


Usage #2: Plus it can be used to remove specific characters from both sides of a String, like below:

	TRIM ( 'm,y' FROM 'Manoj Pandey') as col1,
	TRIM ( 'ma,ey' FROM 'Manoj Pandey') as col2,
	TRIM ( 'm,a,e,y' FROM 'Manoj Pandey') as col3


Thus with the above query you can see that you can trim characters too, by providing leading & trailing characters, but should be in same sequence as your string is.

Also for Col2 & Col3 we have provided Trimming Characters in 2 different ways, but got the same output.

–> Note: I just mentioned above that the leading & trailing characters should be in same sequence. If you provide in different sequence like below you won’t get desired results.

SELECT 'Manoj Pandey' as st,
	TRIM ( 'a,n' FROM 'Manoj Pandey') as Col1,
	TRIM ( 'm,e' FROM 'Manoj Pandey') as Col2,
	TRIM ( 'm,o,y,e' FROM 'Manoj Pandey') as Col3


Like for Col3 you cannot get rid of middle characters (like ‘o’ and ‘n’) until and unless they become leading or trailing characters.

New built-in function STRING_AGG() with option “WITHIN GROUP” – SQL Server vNext (2018)

December 23, 2016 1 comment

In one of my [previous post] I discussed about a new function STRING_SPLIT() introduced in SQL Server 2016, which splits a Sentence or CSV String to multiple values or rows.

Now with the latest CTP 1.x version of SQL Server vNext (I’m calling it “SQL Server 2018”) a new function is introduced to just do its reverse, i.e. Concatenate string values or expressions separated by any character.

The STRING_AGG() aggregate function takes all expressions from rows and concatenates them into a single string in a single row.

It implicitly converts all expressions to String type and then concatenates with the separator defined.

–> In the example below I’ll populate a table with States and Cities in separate rows, and then try to Concatenate Cities belonging to same States:

USE tempdb

CREATE TABLE #tempCityState (
    [State] VARCHAR(5), 
    [Cities] VARCHAR(50)

INSERT INTO #tempCityState

SELECT 'CA', 'Hanford'
SELECT 'CA', 'Fremont'
SELECT 'CA', 'Los Anggeles'
SELECT 'CO', 'Denver'
SELECT 'CO', 'Aspen'
SELECT 'CO', 'Vail'
SELECT 'CO', 'Teluride'
SELECT 'WA', 'Seattle'
SELECT 'WA', 'Redmond'
SELECT 'WA', 'Bellvue'

SELECT * FROM #tempCityState
State	Cities
CA	Hanford
CA	Fremont
CA	Los Anggeles
CO	Denver
CO	Aspen
CO	Vail
CO	Teluride
WA	Seattle
WA	Redmond
WA	Bellvue


–> To comma separate values under a single row you just need to apply the STRING_AGG() function:

	STRING_AGG(Cities, ', ') as AllCities
FROM #tempCityState

-- Use "WITHIN GROUP (ORDER BY ...)" clause to concatenate them in an Order:
	STRING_AGG(Cities, ', ') WITHIN GROUP (ORDER BY Cities) as AllCitiesSorted
FROM #tempCityState


–> Now to Concatenate them by States, you just need to group them by the State, like any other aggregate function:

	STRING_AGG(Cities, ', ') as CitiesByStates
FROM #tempCityState

-- Use "WITHIN GROUP (ORDER BY ...)" clause to concatenate them in an Order:
	STRING_AGG(Cities, ', ') WITHIN GROUP (ORDER BY Cities) as CitiesByStatesSorted
FROM #tempCityState


You can check more about STRING_AGG() on MSDN.

Microsoft released SQL Server vNext (2018) – New features and enhancements in CTP 1.x

November 20, 2016 2 comments

On 16th Nov 2016 Microsoft announced the first Community Test Preview (CTP 1.0) of SQL Server vNext that will run both on Windows & Linux. Not only Linux, but it will be supported on Docker, and macOS (via Docker) too.

For now I’m calling the vNext version as SQL Server 2018. (2018 is not called officially, I’m calling it just to avoid any confusion 🙂 )

This announcement was along with the release of SQL Server 2016 SP1, and the vNext version also supports features added in SQL Server 2016 SP1.

–> Download vNext bits:

To download the SQL Server vNext you can Register and Download the Full version or Free evaluation version (180 days).

Or, directly download the ISO (~2 GB): SQLServerVnextCTP1-x64-ENU.iso

–> Check version and SQL build:

select @@version

Microsoft SQL Server vNext (CTP1) – (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 14393: ) (Hypervisor)

–> New Features & Enhancements:

>> Database Engine:

1. All new features in SQL Server 2016 SP1.

2. New compatibility level 140.

3. Improvements to the way incremental statistics update thresholds are computed.

4. New DMVs added:
   – sys.dm_exec_query_statistics_xml added, to get query execution plan for in-flight requests.
   – sys.dm_os_host_info, to provide operating system information for both Windows and Linux.

5. Enhancements to In-Memory Tables & SPs:
   – Can now use sp_spaceused, sp_rename, CASE, TOP (N) WITH TIES
   – More than 8 indexes

6. Clustered Columnstore Indexes now support LOB columns (nvarchar(max), varchar(max), varbinary(max)).

7. New STRING_AGG() aggregate function has been added.

8. Database roles are created with R Services for managing permissions associated with packages.

9. New Japanese collations are added.

– Features added in CTP 1.1

10. Memory-optimized tables and indexes now support computed columns
   – Full support for JSON functions
   – and CHECK constraints
   – Natively compiled modules now support the CROSS APPLY operator

12. New string functions CONCAT_WS, TRANSLATE, and TRIM are added.
   – and the WITHIN GROUP clause is now supported for the STRING_AGG function.

– Features added in CTP 1.2

13. Online index build and rebuild support for non-clustered Columnstore indexes.

14. Support for SUSE Linux Enterprise Server v12 SP2

15. SQL Server Early Adoption Program (EAP), msdn blog on EAP

– Features added in CTP 1.3

16. Full text search, now available for all supported Linux distros

17. Resumable online index rebuilds

18. Temporal Tables Retention Policy

19. Indirect checkpoint performance improvements

20. Online non-clustered columnstore index buill and rebuild support added

21. Availability Groups enhancements:
   – Cluster-less Availability Groups support added
   – Minimum Replica Commit Availability Groups setting
   – Availability Groups can now work across Windows-Linux to enable cross-OS migrations and testing

22. New DMV sys.dm_db_stats_histogram, for examining statistics

23. 5 new dynamic management views to return information about Linux process:
   – sys.dm_linux_proc_all_stat
   – sys.dm_linux_proc_cpuinfo
   – sys.dm_linux_proc_meminfo
   – sys.dm_linux_proc_sql_maps
   – sys.dm_linux_proc_sql_threads

>> SQL Server Analysis Services (SSAS):

1. 1400 Compatibility level for tabular models.

2. Modern Get Data query and import data features for tabular models.

3. Support for ragged hierarchies with the new Hide Members property.

4. Support for drillthrough by using Detail Rows. This includes the Default Detail Rows expression property and DETAILROWS DAX function.

5. Table-level security, restricts user permissions on table data and table names.

6. New DAX IN function.

7. Encoding hints, an advanced feature used to optimize processing (data refresh) of large in-memory Tabular models.

>> SQL Server Integration Services (SSIS):

   – Support Scale Out of SSIS: to run SSIS on multiple machines. With Scale Out Master and Workers, packages can be distributed to execute on different Workers automatically. Check more details here in MSDN.


>> SQL Server R support: Check full details here in MSDN.

–> Download & Install SQL Server vNext & SSMS:


–> References:

>> MSDN Blog announcement

>> MSDN for SQL Server vNext

>> SQL Server vNext Release Notes