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)
–> 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
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.
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.
So now as SQL Server 2016 is released (on 1-June-2016) and is in market for few days, so Microsoft team has released a new Sample Database “WideWorldImporters” specially for learning and working with new features of SQL Server 2016 and Azure SQL Database.
Till now AdventureWorks was quiet popular Sample Database since SQL Server 2005 to SQL Server 2014, and still will be. And prior to this we had Northwind and Pubs sample databases to work with SQL Server 2000 version.
You can download both the OLTP and OLAP (DW/BI) databases from this GitHub link.
1. WideWorldImporters (OLTP): contains sample tables for OnLine Transaction Processing (OLTP) workloads, as well as Real-time Operation Analytics.
2. WideWorldImportersDW (OLAP, DW/BI): contains sample tables for OnLine Analytical Processing (OLAP) workloads, in Dimensional Model, like Fact and Dimension tables.
3. For Azure SQL Database: you can download the bacpac for both the editions OLTP/OLAP.
–> These Sample Databases are designed in such a way that these can be used to check and evaluate the new Features of SQL Server 2016, like:
1. Temporal Database and Tables
2. Native JSON support
3. ColumnStore Index
4. In-Memory OLTP
5. Row Level Security, Dynamic Data Masking and Always Encrypted
7. Query Store
–> The MSDN Documentation of these sample databases provides you more information on:
1. Installation and Configuration
2. The Database Catalog
3. Use of SQL Server features and capabilities (mentioned in above points)
4. Some Sample Queries (zip file)
–> After Downloading and Restoring the sample tables looks like this in Object Explorer:
Polybase error in SQL Server 2016 : Row size exceeds the defined Maximum DMS row size, larger than the limit of [32768 bytes]
I got an email form one of my reader regarding issues while working with SQL Server 2016 and Polybase, and it is as follows:
I am able to successfully install SQL with Polybase and able to query data in Azure storage but for a table I am getting error.
I am trying to pull data by creating External Data Source connection in SQL enabled Polybase features. I am getting below error as:
Cannot execute the query “Remote Query” against OLE DB provider “SQLNCLI11” for linked server “(null)”. 107093;Row size exceeds the defined Maximum DMS row size: [40174 bytes] is larger than the limit of [32768 bytes]
With the error description its quiet evident that the External tables does not support row size more than 32768 bytes. But still I take a look online and found in Azure Documentation that this is a limitation right now with Polybase. The Azure document mentions:
Wide rows support is not supported yet, “If you are using Polybase to load your tables, define your tables so that the maximum possible row size, including the full length of variable length columns, does not exceed 32,767 bytes. While you can define a row with variable length data that can exceed this figure, and load rows with BCP, you will not be be able to use Polybase to load this data quite yet. Polybase support for wide rows will be added soon. Also, try to limit the size of your variable length columns for even better throughput for running queries.”
Its 1st June 2016 and finally Microsoft has released SQL Server 2016 full and final version, and is available for download!
Exactly a year back the first CTP version of SQL Server 2016 was released and we got a chance to get a glimpse of the new features coming in. Then after every month or two Microsoft kept rolling out the CTP versions with more new features, enhancements, and bug fixes on the previous CTP builds.
–> Download SQL Server:
To download SQL Server 2016 you can Register and Download the SQL Server 2016 Full or free Evaluation version (180 days) here.
Or you can Direct download the DVD ISO file image (~2.1 GB) SQLServer2016-x64-ENU.iso
–> Free Developer Version:
Microsoft on March 2016 announced that going forward the Developer version of SQL Server any release will be free for Developers and Learning purpose. Register and Download the Developer version.
Or you can Directly download the DVD ISO image (~2.1 GB): en_sql_server_2016_developer_x64_dvd_8777069.iso
This Developer version is meant for development and testing only, and not for production environments or for use with production data. For more info please check my previous blog post.
–> One big Update on SSMS:
From now onward SSMS i.e. SQL Server Management Studio will not be part of SQL Server installation. This has to be installed separately via the SSMS download page, or from the Installation Center which will download SSMS from online.
As per Microsoft, this is basically to support the move to make a universal version of SSMS for both SQL Server on-Prem & Azure SQL Database, that will ship every month or so.
Check my previous post on installing SSMS separately.
–> Check new features in SQL Server 2016: https://sqlwithmanoj.com/sql-server-2016-articles/
4. Dynamic Data Masking, Link
5. Stretch Database, Link
6. Query Store, Link
7. Live Query Statistics, Link
8. Enhanced In-Memory OLTP, Link
9. Enhanced ColumnStore Indexes, Link
10. Truncate Table partition
11. Other features like, IF EXIST with DROP stmt, STRING_SPLIT fn, COMPRESS & DECOMPRESS fn, etc.