Jun 26, 2016

Introduction to SQL Server 2016

Chapter 1

SQL Server 2016 is the latest addition of Microsoft SQL Server Database. SQL Server 2016 came with variety of new features and enhancements that deliver breakthrough performance, advance 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 cloud.

Some Enchantment and features are given below.

Faster Queries:

In SQL Server faster queries was the major problem, user want 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 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(t.name)
+ ';' + 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';