Skip to main content

Introduction to SQL Server 2016

Chapter 1

SQL Server 2016 is the latest edition of Microsoft SQL Server Database. SQL Server 2016 came with a variety of new features and enhancements that deliver breakthrough performance, advanced security, integrated reporting, and capabilities. Many features have been incorporated in SQL Server 2016 which introduced first in the cloud in Microsoft Azure SQL Database.  SQL Server includes the capability to dynamically migrate historical data to the cloud.

Some Enchantment and features are given below.

Faster Queries:

In SQL Server faster queries were the major problem, a user wants to data access more as you can give him. 
  1. SQL Server 2016 gives several options to for enabling faster query. Memory- optimization table now support even faster online transaction processing (OLTP) workloads, with better throughput as a result of new parallelized operations.
  2. For analytic workloads, you can take advantage of updateable, clustered columnstore indexes on memory-optimized tables to achieve queries that are up to one hundred times faster.
  3. Not only is the database engine better and faster in SQL Server 2016, but enhancements to the Analysis Services engine also deliver faster performance for both multidimensional and tabular models.
Some features and enhancements have given following.
  1. In-Memory OLTP enhancements
  2. T-SQL enhancements
  3. In-memory analytics
  4. Analysis Services enhancements
In-Memory OLTP enhancements

In-Memory OLTP introduced in SQL Server 2014. 
  1. In-Memory OLTP accelerate transactional workload high concurrency and too many latches by moving data from disk-base table to memory optimize table.
  2. In-memory OLTP can also help improve the performance of data warehouse staging by using nondurable, memory-optimized tables as staging tables. Although there were many good reasons to use memory-optimized tables in the first release of In-Memory OLTP, several limitations restricted the number of use cases for which In-memory OLTP was suitable.
New features for memory-optimized table:
In SQL Server 2016, you can implement the following features in memory-optimization table.
  1. FOREIGN KEY constraints between memory-optimized tables, as long as the foreign key references a primary key.
  2. CHECK constraints.
  3. UNIQUE constraints.
  4. Triggers (AFTER) for INSERT/UPDATE/DELETE operations, as long as you use WITH NATIVE_COMPILATION.
  5. Columns with large object (LOB) types—varchar(max), nvarchar(max), and varbinary(max).
  6. Collation using any code page supported by SQL Server.
  7. Indexes for memory-optimized tables now support the following features:
  8. UNIQUE indexes.
  9. Index keys with character columns using any SQL Server collation.
  10. NULLable index key columns. 
Scaling memory-optimized tables: 
You can put more data in memory-optimized table and also provide better flow capacity to support bigger workload. You can store up to 2 terabytes (TB) of data in memory-optimized table when you create a durable table by using the SCHEMA_AND_DATA option in the CREATE TABLE statement. You can create a memory-optimized table with a row size greater than 8,060 bytes, even without a large object ( LOB ) column. 

            ISBN NVARCHAR(4000),
            Title NVARCHAR(4000),
            CreatedDate DATETIME,
            Price DECIMAL(18,2),
            Qty INT,

 Introducing native client enhancements:
SQL Server 2016 gives facility to enable Multiple Active Result Sets(MARS) when connecting to a memory-optimized table or running a natively compiled stored procedure. This way, the database engine can begin fetching rows from a new result set before completely retrieving rows from an earlier request.

Note : Before use of MARS, you must enable it explicitly in the connection string, like this:

Data Source=<Your Server>; Initial Catalog=<Your Database>; Integrated Security=SSPI; MultipleActiveResultSets=True
In many part MARS support to memory-optimized tables physical table but some differences given below.
  1. If two statement applied to modify one row, a write conflicts occurs and operation fails.
  2. As we know each statement runs under SHANPSHOT iteration and 'is in batched-scoped transaction, the result of an operation performed by one statement is not visible to another statement. However, rollback of one batch-scoped transaction does affect other transactions in the same batch.
  3. A user transaction cannot perform DDL Operation. 
 Exploring T-SQL enhancement :

Alter table support more functionality so that you can make changes to existing memory-optimized tables without needing to first drop table.
Alter Table Statement:

You can use the ALTER TABLE statement to change the table definition and indexes on memory-optimized tables. 

-- Add a column and an index
INDEX ix_CreatedDate(CreatedDate);
-- Alter an index by changing the bucket count
( BUCKET_COUNT = 2000000);
-- Drop an index
ALTER TABLE dbo.Books DROP INDEX ix_CreatedDate;

In most cases, the ALTER TABLE statement runs in parallel and writes only metadata changes to the transaction log as a performance optimization. However, the following single-threaded operations continue to require writing the entire altered table to the transaction log:
  1. Adding or altering a column with a LOB data type.
  2. Adding or dropping a columnstore index.
  3. Any operation affecting an off-row column—a column with a data type of char, nchar, varchar, nvarchar, binary, or varbinary that does not fit in the 8,060-byte row—except if the operation lengthens a column that is already off-row. 
 Natively compiled modules:
Tables and store procedures compiled natively introduced in SQL Server 2014 which enables more efficient query execution and data access for memory-optimized tables. New ability in SQL Server 2016 is the compile other objects like scalar user-defined functions (UDFs), inline table-valued functions (TVFs), and triggers. In addition to the support of new objects, the query surface area for native compilation is expanded and applies not only to natively compiled stored procedures but to all natively compiled modules. You can also alter a natively compiled module rather than drop and re-create it when a change to its functionality is necessary.
Much like you do when creating a natively compiled stored procedure, you must include the NATIVE_COMPILATION and SCHEMABINDING options in the WITH clause when you create a natively compiled scalar UDF. See following example. 

Using LOBs in a natively compiled scalar UDF
            @str1 NVARCHAR(MAX),
            @str2 NVARCHAR(MAX)
RETURN RTRIM(@str1) + LTRIM(@str2);

SQL Server 2016 also supports the following capabilities for natively compiled modules:
  1. Use the OUTPUT clause with the INSERT, UPDATE, and DELETE statements in a natively compiled stored procedure.
  2. Omit the EXECUTE AS clause when you want EXECUTE AS CALLER as the execution context of a natively compiled module. You include an EXECUTE AS clause in a module only if you need to enforce a different execution context.
  3. Use any SQL Server collation in an expression.
  4. Use any of the built-in math and security functions.
  5. Use SELECT DISTINCT in the SELECT clause.
  6. Use OUTER JOIN, EXISTS, IN, and scalar sub-queries in the FROM clause.
  7. Use OR or NOT in the WHERE clause.
  8. Combine result sets by using the UNION or UNION ALL operations.
  9. Configure nested execution of natively compiled modules by using the EXECUTE statement. 
Managing memory-optimized tables: 
We are aware with STATITICS, SQL Server 2016 give facility to update automatically but you have to set database capability 130. Statistics are updated by data sampling rather than by a full scan. If you are creating memory-optimized table in SQL Server 2014 or SQL Server 2016 then run the following script to update STATITICS automatically for tables. 

-- Enable automatic update on the database first, if necessary
-- Set the compatibility level, if necessary
+ quotename(schema_name(t.schema_id))
+ N'.' + quotename(
+ ';' + CHAR(13) + CHAR(10)
FROM sys.tables AS t
WHERE t.is_memory_optimized = 1;
EXECUTE sp_executesql @sql;


Planning data migration to memory-optimized tables: 
SQL Server 2016 includes several tools for helping you plan a migration of data in existing tables to In-Memory OLTP. Reports and tasks in SQL Server Management Studio (SSMS) help you evaluate workloads and create checklists of migration steps. You can also use SQL Server PowerShell or Windows PowerShell to evaluate candidate objects for migration. 
Analysis Services enhancements:

There are two types of modes in Analysis Service to support fast reporting and analytics. 
  1. Multidimensional
  2. Tabular
Understanding multidimensional performance improvements: 
The performance of queries includes an unnatural hierarchy which is faster than its previous version. Although natural hierarchy remains faster yet. Storage structure of an unnatural hierarchy is unchanged. Instead, the engine is now able to execute the query in a more optimal fashion when it encounters a drill-down query. 

--Drilling down one level of a hierarchy
SELECT NON EMPTY Hierarchize(DrilldownMember({{DrilldownLevel({[Product].[Color-Size].[All]},,, INCLUDE_CALC_MEMBERS)}},
FROM [Adventure Works]
WHERE ([Measures].[Reseller Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING,

Understanding tabular performance improvements: 
Tabular-model performance has always been good because of its use of the same columnar technology as columnstore indexes and in-memory OLTP. However, it’s even better in SQL Server 2016 because of DAX optimizations, storage-engine caching improvements, and changes to the way that T-SQL is generated for DirectQuery mode.

The first DAX optimization is a reduction in the number of queries sent to the storage engine from Power BI and Power BI Desktop. Prior to this change, a single chart could potentially send hundreds of queries to the storage engine. Now the Power BI client tools generate a single storage-engine query, whether the storage engine retrieves data from memory or from SQL Server in DirectQuery mode—as long as the query includes only simple measures. Furthermore, a single DAX query can return multiple result sets as intermediate results that Power BI can use across multiple requests.

The next optimization executes queries more efficiently in any client tool, including Microsoft Excel or SQL Server Reporting Services (SSRS) in addition to the Power BI client tools. This efficiency is a result of changes in the following areas that affect measure execution:
  1. Variables As we describe in more detail in Chapter 6, “More analytics,” the use of variables in a DAX expression for measures allows you to reuse logic within the same expression, which can reduce overall execution time.
  2. Conditional expressions Rather than evaluating each branch of an IF or SWITCH conditional expression, a branch with a false condition no longer generates a storage-engine query. 
In-memory analytics

SQL Server 2016 adds more options for performing real-time analytics on rapidly changing in-memory data by adding support for columnstore indexes on memory-optimized OLTP tables.

Reviewing columnstore index enhancements:

1.    An enhancement to clustered columnstore indexes in SQL Server 2016 is the ability to have a nonclustered B-tree index on a table that also has a clustered columnstore index.
2.    Improvement in batch-execution mode, a feature of columnstore indexes that allows results to be processed in batches of approximately 1,000 rows rather than row by row.
3.    SQL Server 2016 also includes support for columnstore indexes in readable secondary databases in Always On Availability Groups.

How to create columnstore index in a disk-based table


DROP_EXISTING=ON when you used this option it drop the colomn before and create new one.

Create non-cluster Columnstore index with filterd condition

ON Sales.Order (CustomerID, ISBN, QTY, Price, ShippingDate)
WHERE ShippingDate < '2016-01-01';

Popular posts from this blog

Query to find stored procedures by nested stored procedure name

Problem: Suppose we have a stored procedure which has been used in several stored procedure, I mean stored procedure usp_proc1 is nested in many stored procedures like below
So I want to find all those queries who containing usp_proc1
Solution: There is a lot of solutions, I'm giving some of them below. If you want to get the only name of the stored procedures then use the following query. Using join query on system tables syscomments and sysobjects we can get the stored procedures name which containing the particular table, nested procs or any other string. FROMsyscommentss

Check for changes to an SQL Server table?

Problem: Suppose your team is working on the under-development project so it might be possible continuous work on the database and perform changes in Table, Stored procedure as per requirement, and daily you have to update the testing server database as per changes are done in developing server database then how it is possible to trace those changes. There are a lot of solutions for this problem which is listed below Solution 1: For SQL Server 2000, 2005 and above use the CHECKSUM command SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMYour_Table_NameWITH (NOLOCK); That will return the same number each time its run as long as the table contents haven't changed. Unfortunately CHECKSUM does not work always properly to detect changes. It is only a primitive checksum and no CRC calculation. Therefore you can't use it to detect all changes, e. g. symmetrical changes result in the same CHECKSUM! Solution 2: 1.Run the following query. Before executing query replace DB_Name with your database name…

Merge and Merge join transformation in SSIS

Using Merge Transformation we can combine two sorted data-set into single data-set basically Merge Transformation used to combines rows from two sorted data flows into one sorted data flow. Following tasks you may perform using Merge Transformation: 1.Suppose we have a scenario like, we need to merge data from a database table and excel means we want to merge data from two different data sources. For such type of scenario, you can use Merge Transformation. 2.If we want to merge data from two same structured tables but exists two different servers. 3.Sometimes we get an error due to data in a row, after correcting errors in the data we can re-merge rows easily. See below explanations may help you to understand Merge Transformation: I do evaluate here, you already know about the data source, data conversion, data flow, task flow, control flow etc. Note:Before Merge transformation, we need to sort the data using Sort Transformation. After sorting data add data path to Merge…