SQL Server 2016 | SQL with Manoj

Microsoft PSS – SQL 2016 Series: It just runs faster | Feb-March updates

March 28, 2016 1 comment

Microsoft PSS Engineers have built a series on “It Just Runs Faster”!

“In the Sep 2014 the SQL Server CSS and Development teams performed a deep dive focused on scalability and performance when running on current and new hardware configurations. The SQL Server Development team tasked several individuals with scalability improvements and real world testing patterns. You can take advantage of this effort packaged in SQL Server 2016. – https://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/”

SQL 2016 will run faster in many ways, without changing code.

The series kickoff is here:

https://blogs.msdn.microsoft.com/psssql/2016/02/23/sql-2016-it-just-runs-faster-announcement/

The first article in this series came out the end of February:

SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better
https://blogs.msdn.microsoft.com/psssql/2016/02/25/sql-2016-it-just-runs-faster-dbcc-scales-7x-better/

The announcements so far in March are:

SQL 2016 – It Just Runs Faster: DBCC Extended Checks
https://blogs.msdn.microsoft.com/psssql/2016/03/01/sql-2016-it-just-runs-faster-dbcc-extended-checks/

SQL 2016 – It Just Runs Faster: Native Spatial Implementation(s)
https://blogs.msdn.microsoft.com/psssql/2016/03/03/sql-2016-it-just-runs-faster-native-spatial-implementations/

SQL Server Parallel Query Placement Decision Logic
https://blogs.msdn.microsoft.com/psssql/2016/03/04/sql-server-parallel-query-placement-decision-logic/

SQL 2016 – It Just Runs Faster: TVPs with Spatial Column(s)
https://blogs.msdn.microsoft.com/psssql/2016/03/08/sql-2016-it-just-runs-faster-tvps-with-spatial-columns/

SQL 2016 – It Just Runs Faster: Spatial Index Builds Faster
https://blogs.msdn.microsoft.com/psssql/2016/03/10/sql-2016-it-just-runs-faster-spatial-index-builds-faster/

SQL 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases
https://blogs.msdn.microsoft.com/psssql/2016/03/15/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/

SQL 2016 – It Just Runs Faster: Automatic TEMPDB Configuration
https://blogs.msdn.microsoft.com/psssql/2016/03/17/sql-2016-it-just-runs-faster-automatic-tempdb-configuration/

SQL 2016 – It Just Runs Faster: LDF Stamped
https://blogs.msdn.microsoft.com/psssql/2016/03/22/sql-2016-it-just-runs-faster-ldf-stamped/

SQL 2016 – It Just Runs Faster: Instant File Initialization
https://blogs.msdn.microsoft.com/psssql/2016/03/25/sql-2016-it-just-runs-faster-instant-file-initialization/

SQL 2016 – It Just Runs Faster: Automatic Soft NUMA
https://blogs.msdn.microsoft.com/psssql/2016/03/30/sql-2016-it-just-runs-faster-automatic-soft-numa/

Check my blog posts on most of the new features released in SQL Server 2016 here

SQL Server 2016 – Articles

Categories: SQL Server 2016 Tags: SQL Server 2016

My whitepaper published on – Top 8 features of SQL Server 2016

March 14, 2016 Leave a comment

I am super excited to announce the availability of my Whitepaper at Microsoft IT Showcase on:

Microsoft IT looks at the top eight features of SQL Server 2016

Download : Word | PDF

Summary:

1. In-Memory OLTP helps ESBI meet their users’ business requirements for increased agility.

2. Columnstore Indexes reduce the amount of time it takes to run and render SRSS reporting data.

3. Temporal data reduces the amount of support tickets received from the field due to inaccurate data.

4. Row-Level Security provides a more reliable and standardized method to easily control which users can access data.

5. Dynamic Data Masking helps limit exposure of sensitive data, preventing users who should not have access to the data from viewing it.

6. Query Store provides better insight into the performance differences caused by changes in query plans.

7. Active Query Statistics allows a view of active query execution plans and helps identify and fix blocking issues while queries are running.

8. SQL Stretch Database helps improve performance to frequently used data while preserving access to archived data.

Download : Word | PDF

Please check my other posts on SQL Server 2016

Categories: Microsoft, SQL Server 2016 Tags: SQL Server 2016, Whitepaper

New in-built Table-Valued Function STRING_SPLIT() in SQL Server 2016 – to split strings

March 10, 2016 5 comments

Till now it was bit tricky to split a Sentence or CSV String to multiple values or rows, and we used different logic to do the same.

In my [previous post] I blogged similar logic to Split a String and Combine back by using some XML syntax.

In SQL Server 2016 this has been made simpler by using a new function STRING_SPLIT(), let’s see this with a simple example:

SELECT * FROM STRING_SPLIT('My name is Manoj Pandey', ' ')

This will split all the words in the sentence separated by a whitespace in different rows:

Here is the syntax for the same:

STRING_SPLIT ( string , separator )

Please note: that the separator should be a single character expression, so this should not be an empty string, like:

SELECT * FROM STRING_SPLIT('My name is Manoj Pandey', '')

Will result into an error:

Msg 214, Level 16, State 11, Line 3
Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

–> Let’s check one more example:

We have a comma separated Cities list for each State as a row in a table:

CREATE TABLE #tempCityState (
	[State] VARCHAR(5), 
	[Cities] VARCHAR(50)
)

INSERT INTO #tempCityState
SELECT 'AK', 'Nashville,Wynne'
UNION ALL
SELECT 'CA', 'Fremont,Hanford,Los Anggeles'
UNION ALL
SELECT 'CO', 'Aspen,Denver,Teluride,Vail'

Now, lets just use the simple function STRING_SPLIT() with CROSS APPLY operator, like:

SELECT [State], value
FROM #tempCityState
CROSS APPLY STRING_SPLIT([Cities], ',')

Will give you following output:

–> And if I compare the performance of this function with the earlier approach I mentioned in my [previous post]:

Run both the queries by enabling Actual Execution plan (Ctrl + M):

SELECT [State], value
FROM #tempCityState
CROSS APPLY STRING_SPLIT([Cities], ',')

SELECT A.[State], Split.a.value('.', 'VARCHAR(100)') AS City
FROM (SELECT [State], CAST ('<M>' + REPLACE([Cities], ',', '</M><M>') + '</M>' AS XML) AS String
    FROM  #tempCityState) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)
ORDER BY 1,2

I can see that the STRING_SPLIT() gives me better performance compared to the other:

Categories: SQL Built-in Functions, SQL Server 2016, SQL Tips Tags: Split String, SQL Server 2016, STRING_SPLIT

SQL Server 2016 RC0, first Release Candidate is available for download

March 8, 2016 1 comment

Microsoft (on 7-March-2015) announced the first SQL Server 2016 release candidate, SQL Server 2016 Release Candidate (RC) 0. With this release SQL Server adds rich set of capabilities, including Real-time Operational Analytics, rich Visualizations on mobile devices, built-in Advanced Analytics, new advanced Security technologies, and new Hybrid scenarios allowing you to securely Stretch data to the cloud.

[Register and Download the CTP 3.3 Evaluation version (180 days) here]

–> Direct download link (~2.6 GB):

– Download the single ISO: SQLServer2016RC0-x64-ENU.iso

– Or download both EXE & BOX files:
– Box file SQLServer2016-x64-ENU.box
– EXE file SQLServer2016-x64-ENU.exe

– Download SSMS: SSMS-Full-Setup.exe

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2016 (RC0) – 13.0.1100.288 (X64)
Feb 29 2016 23:19:56
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 10586: )

Please Note: Management Studio (SSMS) is no longer bundled with the ISO or CAB files, and will need to be installed separately, link mentioned above for RC0.

–> The RC 0 has some awesome new features:

1. Database scoped configuration: a new database level object that holds optional configuration values that affect the behavior of the application code at the database level. This statement modifies the default SQL Server 2016 Database Engine behavior for a particular database. A generic mechanism for creating database configuration(s) at creation time is not provided. These options are:
   – Clear procedure cache.
   – Set the MAXDOP parameter to an arbitrary value (1,2, …) for the primary database based on what works best for that particular database and set a different value (e.g. 0) for all secondary database used (e.g. for reporting queries).
   – Set the query optimizer cardinality estimation model independent of the database to compatibility level.
   – Enable or disable parameter sniffing at the database level.
   – Enable or disable query optimization hotfixes at the database level.

2. PolyBase enhancements includes support for:
– The latest Cloudera distribution CDH5.5 on Linux.
– Public containers and blobs in Azure blob storage.

3. In-Memory OLTP enhancements includes:
   – Parallel scan of non-clustered indexes.
   – Reduced downtime during upgrade.
   – Built-in function @@SPID is now supported natively compiled T-SQL modules and in constraints on memory-optimized tables
   – Log-optimized and parallel ALTER (ALTER TABLE operations now run in parallel).

4. STRING_SPLIT() and STRING_ESCAPE() function, new in-built Table-Valued Functions:
– STRING_SPLIT, Splits input character expression by specified separator and outputs result as a table.
– STRING_ESCAPE, Escapes special characters in texts and returns text with escaped characters.

5. Support for UTF-8 data to export and import.
– Data stored in a UTF-8 encoded file can now be imported into SQL Server and exported from SQL Server into a UTF-8 encoded file, using BCP utility, BULK INSERT and OPENROWSET.

6. JSON_MODIFY() new function:
– To update the value of a property in a JSON string and return the updated JSON string.

7. Stretch Database enhancements:
– New version of Stretch Database on Azure that supports 60 TB of data
– Azure Active Directory authentication, The option to use a federated service account for SQL Server to communicate with the remote Azure SQL Database server when certain conditions are true no longer requires a trace flag.

8. ColumnStore Index enhancements:
   – Compression delay. This new option minimizes the impact of the transactional workload on real-time operational analytics.
   – GROUP BY performance enhancement, aggregate pushdown for GROUP BY clause when run against a nonclustered columnstore index.
   – New alter table option, now you can create a columnstore index on an existing memory-optimized table.

9. SQL Server Management Tools (SSMS): SQL Server Management Tools is no longer installed from the main feature tree, as mentioned above.

–> Few more enhancements in other areas:

1. AlwaysOn Availability Groups add support for:
– Distributed Availability Groups.
– Streaming seeding of replicas.

2. SSAS (Analysis Services) add support for :
   – Display folders in Analysis Services.
   – PowerShell support for Tabular models.
   – SSIS support for Tabular models.
   – Tabular Object model for Tabular models.
   – more at Analysis Services blog.

3. SSRS (Reporting Services) support for:
   – Subscribe to reports to receive them in your email inbox, download, rename, move, and delete reports and other catalog items.
   – Manage shared datasets.
   – Manage data caching and refresh.
   – Create “linked” reports with different default parameter values.
   – Pin to a dashboard in a group when pinning a report chart, gauge, map, or image to a Power BI dashboard.
   – Choose the currency for monetary values when creating a KPI or mobile report.
   – more at Reporting Services blog.

4. SQL Server Integration Services (SSIS)
   – SSIS Projects in Visual Studio SQL Server Data Tools (SSDT) can now target 2012, 2014 and 2016 versions of SQL Server by switching a project level property between 2012, 2014 and 2016 SQL server.
   – SSIS now supports setting a server wide customized logging level.
   – The SSIS HDFS connector now supports the ORC file format, in addition to CSV and Avro.
   – more at Integration Services blog.

I’ve covered very few and important points here, you can check all the updates here in [SQL Server blog for RC 0] with details.

For all these new features released in SQL Server 2016, check my blog posts here.

So, download the Preview today and start playing with the new features and plan your DB migration/upgrade.

Do check & Like my FB Page.

Categories: SQL Server 2016 Tags: Install SQL Server, SQL Server 2016

SQL Server 2016 CTP 3.3 update is here | download now

February 5, 2016 Leave a comment

Microsoft (on 4-Feb-2015) announced the CTP 3.3 update of the recently and initially released Community Technology Preview (CTP) 2.x & 3.0 versions of SQL Server 2016.

[Register and Download the CTP 3.3 Evaluation version (180 days) here]

–> Direct download link (~2.6 GB):

– Download the single ISO: SQLServer2016CTP3.3-x64-ENU.iso

– Or download both EXE & BOX files:
– Box file SQLServer2016-x64-ENU.box
– EXE file SQLServer2016-x64-ENU.exe

–> Check version and SQL build:

select @@version

Microsoft SQL Server 2016 (CTP3.3) – 13.0.1000.281 (X64) Jan 28 2016 15:11:40 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 10586: )

–> This CTP 3.3 release has major enhancements on:

1. Stretch Database improvement includes:
   – Alter and drop index support for stretch tables.
   – Add, alter and drop columns support for stretch tables.
   – Query performance improvement.
   – Azure Stretch database edition preview with support for up to 60TB
   – Point-in-time restore and geo-failover support.
   – Improved SSMS visualization with StretchDB icons.
   – Added support for stretching using federated accounts in SSMS.

2. In-Memory OLTP enhancements, which dramatically improves transaction processing performance.
   – Automatic update of statistics on memory-optimized tables
   – Sampled statistics for memory-optimized tables
   – Use of LOB types varchar(max), nvarchar(max), and varbinary(max) with built-in string functions (‘+’, len, ltrim, rtrim and substring) in natively compiled modules, and as return type of natively compiled scalar UDFs.
   – Support also larger rows using types varchar(n), nvarchar(n) and varbinary(n)
   – OUTPUT clause can now be used with INSERT, UPDATE and DELETE statements in natively compiled stored procedures.

3. Foreign Key enhancements , increased the number of supported incoming foreign key REFERENCES to a table:
– Till SQL Server 2014 the recommended maximum was 253, now the new maximum is 10,000 references, while maintaining good performance for DML operations in both the referencing and the referenced table.

4. Autostats enhancements, Previously, statistics were automatically recalculated when the change exceeded a fixed threshold.
– Now the algorithm is refined such that it is no longer a fixed threshold, but in general will be more aggressive in triggering statistics scans, resulting in more accurate query plans.

–> Few more enhancements in other areas:

1. SSAS (Analysis Services) DirectQuery models, Tabular Models running in DirectQuery mode now also allows:
   – DAX filters when defining roles and creation of calculated columns.
   – Apply row level security to a DirectQuery model.
   – Translating your tabular model into different languages to be consumed by any client tool connecting to SSAS.
   – and many more, [check here].

2. SSRS (Reporting Services) web portal, updated preview of the new web portal now enables you to:
   – add the KPIs and reports you use to your Favorites, to create and edit shared data sources for your KPIs and reports, and to perform other management tasks.
   – access the reports you need on your Favorites page – without the clutter.
   – and many more, [check here].

3. SQL Server Management Studio (SSMS) Jan 2016 Release, [check here].

I’ve covered very few and important points here, you can check all the updates here in [SQL Server blog for CTP 3.3] with details.

For all these new features released in SQL Server 2016, check my blog posts here.

So, download the Preview today and start playing with the new features and plan your DB migration/upgrade.

Do check & Like my FB Page.

Categories: SQL Server 2016 Tags: Install SQL Server, SQL Server 2016

Newer Entries Older Entries