Advertisements

Archive

Posts Tagged ‘SQL Server 2017’

Microsoft released SQL Server 2017 CTP 2.0 – new features, enhancements & what’s new for Linux

April 19, 2017 1 comment

Today on 19th April 2017 Microsoft released the CTP 2.0 version of SQL Server 2017.

As announced earlier with the first CTP release, the new SQL Server 2017 will run both on Windows & Linux. Not only Linux, but it will be supported on Docker, and macOS (via Docker) too.
 

–> Download SQL Server 2017 bits:

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

Or, directly download the ISO (~1.7 GB): SQLServerVnextCTP2.0-x64-ENU.iso
 

–> Check version and SQL build:

select @@version

Microsoft SQL Server vNext (CTP2.0) – 14.0.500.272 (X64)
Apr 13 2017 11:44:40
Copyright (C) 2017 Microsoft Corporation. All rights reserved.
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 14393: ) (Hypervisor)

 

–> New Features & Enhancements:

1. Support for Graph Data to model many-to-many relationship, with Nodes and Edges Table types.

2. Resumable online index rebuild, allows you to resume an online index rebuild operation from where it stopped after a failure. You can also Pause and later Resume an Online index rebuild operation.

3. IDENTITY_CACHE option for ALTER DATABASE SCOPED CONFIGURATION, to avoid gaps in the values of identity columns in case a server restarts unexpectedly or fails over to a secondary server.

4. Batch Mode Adaptive Join to improve plan quality.

5. Interleaved Execution for multi-statement T-SQL TVFs to improve plan quality.

6. Adaptive Query Processing, for automatically running database queries efficiently.

7. SQL Server Machine Learning Services (till now, SQL Server R Services), added support for the Python language with existing ‘R’.

8. Run the Python language in-database to scale and accelerate machine learning, predictive analytics and data science scripts

9. And for SQL Server with Linux:
    – Additional SQL Server Agent capabilities
    – Listener for Always On availability groups

… I’ll discuss about all these features in my coming posts.
 

–> Feature Selection Page:


 

–> You can check other features released in CTP 1.x here.
 

–> References:

>> SQL Server 2017 official Page

>> MSDN Blog announcement

>> Docs for SQL Server 2017


Advertisements

New built-in function CONCAT_WS() in SQL Server 2017

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.
 

Syntax:

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]
GO

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

concat_ws-01
 

–> 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.

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

concat_ws-02


New built-in function TRANSLATE() in SQL Server 2017

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 & 2017 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.
 

Syntax:

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}', '[]{}', '()()');
GO

Output:

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:

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

 

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 2017

December 26, 2016 1 comment

 
If you are thinking the new TRIM() function in SQL Server 2017 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:

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

trim-1

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

SELECT 
	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

trim-2

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

trim-3

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 2017

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 2017”) 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
GO

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

INSERT INTO #tempCityState

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

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:

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

-- Use "WITHIN GROUP (ORDER BY ...)" clause to concatenate them in an Order:
SELECT 
	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:

SELECT 
	State, 
	STRING_AGG(Cities, ', ') as CitiesByStates
FROM #tempCityState
GROUP BY State

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


 

You can check more about STRING_AGG() on MSDN.