RSS

Tag Archives: New Features

Top 5 new features in SQL Server 2012 for developers

Introduction:

Microsoft SQL Server 2012 introduces many features that help database administrators, database developers, and BI developers.

In this article, I will cover some of the new features for database developers in these main points:

  • Database Engine Improvements
  • Improvements to SQL Server Management Studio Debugging
  • Changes to the Scope of Objects
  • Conclusion
  • References

Database Engine Improvements:

  1. File Tables:When we open up SQL Server Management Studio, one of the first changes we notice for SQL Server 2012 is the addition of a new type of table called a File Table.

    File table allows us to make a connection between windows share and a database table such that any file that appears in the share will become a row item in the table.

    It allows us to run queries that tell us how many files we have in that shared location, what type of files, what size the files are, etc….

    Setting this up is a multiple step process:

    • Enable file stream: is The first step we have to do at the instance level.We will do that with the configuration manager tool, open properties of the instance we are interested in, and there is a tab for file stream, in there we should click on all of the checkboxes(enabling all features) as below

word-image-51

 

To complete the article check it HERE

 
Leave a comment

Posted by on February 18, 2017 in New Feature

 

Tags: , , , ,

Concept and basics of Temporal tables in SQL Server 2016

In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:

  • Introduction
  • What is a temporal table?
  • Why Temporal table?
  • How does temporal table work?
  • Consideration and limitation
  • Temporal tables vs CDC
  • Creation and configuration
  • Clean up and removal
  • References

Introduction

AS we know, Microsoft released SQL Server 2016 RTM version (13.00.1601.5) and in November of 2016 updated it by the latest CU (Security Bulletin MS16-136 (CU) KB #3194717) (13.0.2186.0) you can check this update from here.

To complete this artcile please check it here in SQLShack 

 
Leave a comment

Posted by on November 25, 2016 in SQL Server 2016

 

Tags: , , , ,

Live Query Statistics in SQL Server 2016

Today we have very good features it will help us in our daily work (Performance troubleshooting and for the debugging of queries) as we know to tune any SQL Server query you should Check first the Execution plan to decide from where the problem from SQL Server 2005 to SQL Server 2014 we have two types of Execution plan:

  • Estimated Execution Plan: Giving you an idea of how SQL Server will most likely perform query execution
  • Actual Execution Plan       : This will show you things that might hint at “out-of-date” statistics etc. But to get this, you must run the query – which can take a long time.

So before if we have case one Query returned Millions of record and we need to check the Query execution plan for it we will go directly for the Estimated Execution plan because actual Execution plan if we need to see it we will wait more time to the query finish and return all the result but Now in SQL Server 2016 we have new Execution plan type ” Live Query Statistics ”  this new features it will he;p us to see the Execution plan when the Query running and the Effect of the Query on the Execution plan step by step from more points:

  • Execution Query Percentage
  • Actual Number of Rows
  • elapsed time
  • operator progress

What I use here in my demo is simple Query return more than 8 Millions of records if I wait for this query to return all this data to see the Actual Execution plan I will wait around 20 Minutes but now in SQL Server 2016 I will See the Execution plan the Actual Execution Plan Live with Live Query Statistics 

SELECT *
FROM sys.all_columns tmp1
Cross JOIN sys.all_columns tmp2

Live Query3

Live Query1 Live Query2

To know More about new feature in SQL Server 2016 Keep following us and to check the previous posts in SQL Server 20116 you can check it from HERE

 
Leave a comment

Posted by on November 8, 2015 in SQL Server 2016

 

Tags: , , , , , , , ,