November | 2016 | SQL with Manoj

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

November 20, 2016 5 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 2017. (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) – 14.0.1.246 (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.

–> For new Features released in CTP 2.0 check here.

–> Download & Install SQL Server vNext & SSMS:

–> References:

>> MSDN Blog announcement

>> MSDN for SQL Server vNext

>> SQL Server vNext Release Notes

Now “CREATE OR ALTER” Stored Procedure, Function, View, Trigger with SQL Server 2016

November 19, 2016 2 comments

SQL Server 2016 release was packed with lot of new features, and I tried to cover most of them, [check here]. This includes some of the major new features like, Polybase, Temporal Tables, JSON support, Stretch DB, Row Level Security, Dynamic data Masking, etc. are very unique to the other Database systems in competition.

But Microsoft’s SQL Server team also keeps on adding few features in every release which were already there in other Database systems, so that developers could use those and make their life easier, like the new IF EXISTS option with DROP & ALTER statements I already discussed in my [previous post].

Now, with the recent Service Pack 1, one more feature has been added, which developers (mainly from the Oracle background) were missing from long time, and that is CREATE OR ALTER option while creating programming modules, like:

1. Stored Procedures (SP)

2. Functions (UDFs)

3. Views

4. Triggers

–> Now you can create a new Stored Procedure without checking its existence, simply by using the new CREATE OR ALTER option, like below:

CREATE OR ALTER PROCEDURE dbo.spgetEmployeeDetails
	@EmpID INT
AS
BEGIN
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName 
	FROM Person.Person
	WHERE BusinessEntityID = @EmpID
END
GO

… you can execute the above code multiple times and it won’t fail. First time this CREATEs the SP, next time it will ALTER it.

–> Previously you need to add an IF EXISTS() condition to check if the SP already exists or not. If exists then drop and then create a new SP, like:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spgetEmployeeDetails')
	DROP PROCEDURE dbo.spgetEmployeeDetails
GO

CREATE PROCEDURE dbo.spgetEmployeeDetails
	@EmpID INT
AS
BEGIN
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName 
	FROM Person.Person
	WHERE BusinessEntityID = @EmpID
END
GO

Categories: SQL Server 2016 Tags: CREATE OR ALTER, SQL Server 2016

SQL Server 2016 1st Service Pack (SP1) is out (download, new features & enhancements)

November 17, 2016 4 comments

Almost 6 months back i.e. on 1st June 2016 Microsoft released SQL Server 2016 RTM, i.e. full and final version, which you can [check and download here].

Yesterday (i.e. 16-Nov-2016) Microsoft released the 1st Service Pack (SP1) of SQL Server 2016.

–> Download:

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

… or you can Download the new Setup utility here, which provides you option to do a Basic or Custom installation, or download the ISO or CAB file (~2.5 GB).

… or you can also just download the Service Pack (SP1) (~550 MB), instead of the whole Setup (~2.5 GB).

–> What’s new SP1:

1. Features which were only available in Enterprise edition are now enabled in Standard, Web, Express, and LocalDB editions, link.

2. List of Bugs and issues fixed, link.

3. CREATE OR ALTER syntax for Stored Procedures, Views, Functions, and Triggers.

4. DBCC CLONEDATABASE (source_database_name, target_database_name), with optional WITH NO_STATISTICS, NO_QUERYSTORE. Creates a duplicate database by cloning Schema, metadata and statistics, without the data.

5. OPTION (USE HINT(‘hint1’, ‘hint2’)), support for a more generic query hinting is added, link.

6. Post this Service Pack (SP1) Parallel INSERTs in INSERT..SELECT to local temporary tables is disabled by default and will require TABLOCK hint for parallel insert to be enabled.

7. New DMVs are added, and some enhanced:
   – sys.dm_exec_valid_use_hints to list hints
   – sys.dm_exec_query_statistics_xml to return showplan XML transient statistics
   – sys.dm_db_incremental_stats_properties to check incremental statistics for the specified table
   – New column instant_file_initialization_enabled is added to sys.dm_server_services, to allow DBAs to programmatically identify if Instant File initialization (IFI) is in effect at the SQL Server service startup.
   – New column estimated_read_row_count is added to sys.dm_exec_query_profiles
   – New columns sql_memory_model and sql_memory_model_desc are added to sys.dm_os_sys_info, to provide information about the locking model for memory pages, and to allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time.