Changes to SQL Server 2016 Management Studio(SSMS)

With each new release of SQL Server, there are many new features that get introduced. Microsoft has put lot of effort in improving the SQL Server Management Studio as well with the new releases to support the new features that get introduced. Also, there has been lot of support improvements for Azure integration and management with SSMS. Previously there was not much, DBAs were able to make changes to Azure databases from SQL Server Management, but with the release of SSMS 2016, there has much much closer integration for managing SQL Azure instances and databases. For those who are not aware, we can download and install SQL Server Management Studio (SSMS) as separate individual download and install just SSMS on a client machine or on a server. Some of the new features support for SQL Server Management Studio(SSMS) include,

– Addition of human-readable error messages. With this, now when there is an error while performing an operation using SSMS, we get more meaningful error, which will be of great help in troubleshooting, instead of wondering or decoding on what the error is about, as like in some of the previous versions of SQL Server Management Studio.
– Stretch database wizard improvements which adds support for predicates.
– SQL Server 2016 management Studio now allows us to create ER Entity-Relationship Diagrams for SQL Azure Databases without the need for any external tool.
– Now we can see some new options when we right click on the SQL Azure database like “Open in Management Portal”, new “Reports” to view “Transaction Performance Analysis Overview”.
– New options supported when we right click on the user table like, Design(now we can design SQL Azure database tables from SSMS), option to choose selecting or edition top x rows, options for graphical interface to create and administer Full-Text indexes on SQL Azure database tables.

– Database properties now have additional properties or settings like, in the options tab, there is a section for Azure which has options which allows us to change the database edition, the Service Level Objective and the maximum database size.
– There have been many other options enabled in SQL Azure database and table properties which allows us to manage or make changes to database or tables of SQL Azure database and tables, just like regular on premise SQL Server database or table.
– Improvement in the AlwaysEncrypted Powershell commandlet to add key encryption APIs.
– Some known issues has been fixed, like to turn off IntelliSense in the SSMS toolbar, if it has been disabled in the Tools,Options dialog.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Basics of Temporal Tables in SQL Server 2016

SQLServerF1

Microsoft has introduced many new features with their new release of SQL Server 2016. One of the such interesting feature released with SQL Server 2016 is Temporal Tables. Temporal Tables are also known as system-versioned tables. In a single liner, these Temporal Tables allows SQL Server to automatically keep history of the data in the table instead of just the current data which we see in regular or temporary tables or variables. This Temporal Tables is a database feature, introduced newly with SQL Server 2016. Although this is called as system-versioned temporal table, but this is a new type of user table in SQL Server 2016, which is designed to keep a full history of data changes in the table and allows an easy point in time analysis of the data. You might wonder, why this type of temporal table is called as a system-versioned temporal table, it is because the period/time of validity for each row is managed by the system, which is the database engine.

Temporal tables were introduced in the ANSI SQL 2011 standard, which is now adopted by Microsoft SQL Server 2016 onwards. To understand more about the Temporal tables, we can compare it with regular user table to understand its behavior, which is different from a normal user table. In a normal user table, we insert the data and we can see the inserted data using select statement, but once we issue a delete or update statement, we will now see the latest data only with the new select statements, but with Temporal tables, we can query and get the data which was deleted or the data which was modified and what was the old value of the data. Taking a real time example, suppose we have a table with one column and it has one row with a data value of ABCD, now we updated this data from ABCD to ABC, now in a normal user table, we can only see ABC value, but with a Temporal tables we can actually see both the old value ABCD and ABC. This is possible by storing the history of the data changes and its values in a history table. History table has the old data and also the start and end times to identify when the data was present in the Temporal tables, thus allowing us to see the data at a older given point of time.

Now, as we have some basic idea about the Temporal tables, lets try to understand further about how to create and query these Temporal tables. Before, we proceed with seeing, how to create these Temporal tables, as like any other feature or object, there are some pre-requisites for creating Temporal tables.
– It is mandatory for a temporal table to have a primary key defined.
– Two additional columns are required to be defined while creating the temporal tables, which are used to record the start and end date and times. These two columns must be of datetime2 data type. These columns are referred to as period columns. These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.
– These tables cannot be part of In-memory OLTP feature.
– There are some restrictions on types of triggers that can be created on these tables.

CREATE TABLE dbo.TestTemporalTable
	(ID int primary key,
	Col1 int
	Col2 int
	SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
	SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
	PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) 
        WITH(SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.TestTemporalTableHistory));

If we don’t specify and History_table, the SQL Server creates a history table with its own name like dbo.MSSQL_TemporalHistoryFor_xxx, where xxx is the object id of the main table. In GUI, the TestTemporalTable is shown under the tables list, where as the TestTemporalTableHistory will be shown under as a subset of TestTemporalTable. TestTemporalTableHistory has same columns as the actual temporal table, but any constraints will be removed. Also, we can create different indexes on TestTemporalTableHistory and TestTemporalTable tables based on the usage of these tables in the application and having different indexes, will greatly help improving the performance. Also, this is not compared to the CDC, which mainly uses transaction log file to track the changes.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

SQL Server 2014 Developer Edition is Free From Now

As a DBA or Developers it is important to have a SQL Server instance to be able to install and use it to understand the working and usage for the applications. So far, DBAs mostly rely on the free Evaluation edition of SQL Server provided by the Microsoft team. This Evaluation edition expires after 180 days, after which we need to purchase a license and upgrade the Evaluation edition to licensed edition depending on our requirement. Microsoft offers different editions for different purposes, like Enterprise edition for important and critical production applications, Standard edition for less critical applications to reduce the cost of the licensing, Developer edition specifically for developers for development and unit testing the applications before moving to production environment. Previously, most developers preferred buying developer edition as the cost is less and is is specifically released for the purpose and it consists of all features available in enterprise edition and also it could be easily upgraded to enterprise edition if required.

Starting with SQL Server 2014, Microsoft has made a decision to make the SQL Server developer edition free of cost for the developers through Visual Studio Dev Essentials members. Once you login as Visual Studio Dev Essentials members, you will be able to download the SQL Server Developer edition and use it for free of cost. This decision was taken inorder for developers to leverage the capabilities that SQL Server 2014 and higher has to offer for their applications and also this is considered as another step for making SQL Server more accessible to the users which helps in spreading the SQL Server in more companies. However, it is important to keep in mind that SQL Server Developer Edition is still specifically for development and testing purposes only, and not to be used in production environments or not to be used with any production data.

Few important things to keep in mind are below.
– This only includes SQL Server Developer Edition and does not include Operating System license or license for any other products.
– SQL Server 2016 developer edition is also will be made available free under same program and this will mostly be continued with the future releases of SQL Server.

In case you are not part of Visual Studio Dev Essentials members, then you may either buy SQL Server developer edition or can try other options available in the market like SQL Server Evaluation edition. If the application is small and not so critical, you may even choose trying SQL Server Express edition. Hope you will get your free developer edition and explore the new features available like InMemory OLTP and other features.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 
1 2