Advertisements

Archive

Posts Tagged ‘SQL Server 2017’

SQL Server 2017 RTM, download, and cool “New Features” !!!

November 16, 2017 Leave a comment

 
On 2nd October 2017 Microsoft released the full & final General Available (GA) 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 RTM bits:

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

–> Download SSMS 17.x bits: download here
 

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 16299: ) (Hypervisor)

 

–> Here are few few “Top Features” added in SQL Server 2017:

1. SQL Server on Linux: Now run SQL Server on all platforms, Windows, Linux, Mac, docker.

2. New Graph Database: for modeling many-to-many relationships.

3. Resumable online index rebuild: Resumes an online index rebuild operation from where it stopped after a failure or a pause.

4. Adaptive query processing: feature optimizes strategies to your application workload in runtime condition.

5. IDENTITY_CACHE option: to avoid gaps in identity values in case of server restarts or failover.

6. SQL Server Machine Learning Services: which added Python support, with existing R language.

7. SSIS “Scale Out” and HA (High Availability): execute SSIS packages across multiple servers.

 

–> For a full list of various features already rolled out with previous CTP versions, please check my previous posts below:

– All new features added in SQL Server 2016 SP1, [link].

– New features added in:

   – SQL Server 2017 CTP 1.x.

   – SQL Server 2017 CTP 2.x.

   – SQL Server 2017 RC1
 

–> Videos on SQL Server 2017

Download & Install SQL Server 2017 & SSMS on Windows

Install SQL Server on Linux
 

–> References:

>> SQL Server 2017 official Page

>> Docs for SQL Server 2017


Advertisements

SQL Server 2017 Release Candidate (RC1, full & final version) is available for download

July 21, 2017 1 comment

 
On 17th July 2017 Microsoft released the full & final Release Candidate 1 (RC1) 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): SQLServer2017RC1-x64-ENU.iso
 

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2017 (RC1) – 14.0.800.90 (X64)
Jul 11 2017 07:03:16
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: This Release Candidate version is the final version of SQL Server 2017 and adds following features:

1. SQL Server on Linux supports Active Directory Authentication, which enables domain-joined clients on either Windows or Linux to authenticate to SQL Server using their domain credentials and the Kerberos protocol.

2. SQL Server on Linux can use TLS to encrypt data that is transmitted across a network between a client application and an instance of SQL Server.

3. Added more model management capabilities for R Services on Windows Server, including External Library Management. The new release also supports Native Scoring.

4. Additional DMVs, enabling dependency analysis and reporting (DISCOVER_CALC_DEPENDENCY, MDSCHEMA_MEASUREGROUP_DIMENSIONS).

5. Added support for SSIS scale out in HA environments, customers can now enable Always On for SSIS.

6. Features already rolled out in previous CTP versions:

– All new features added in SQL Server 2016 SP1, [link].

– New features added in SQL Server 2017 CTP 1.x, [link].

– New features added in SQL Server 2017 CTP 2.x, [link].
 

–> Videos on SQL Server 2017

Download & Install SQL Server 2017 & SSMS on Windows

Install SQL Server on Linux
 

–> References:

>> SQL Server 2017 official Page

>> Docs for SQL Server 2017


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

April 19, 2017 3 comments

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


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 !!!