new CTP 2.1 update for SQL Server 2016 available for download


Today Microsoft announced an update of the recently released Community Technology Preview (CTP) 2 version of SQL Server 2016, the CTP 2.1.
 

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

 

Direct download link:
SQLServer2016CTP2.1-x64-ENU.box
SQLServer2016CTP2.1-x64-ENU.exe
 

As per the Microsoft [SQL Server Blog], now people do not have to wait for several months for the release of next CTP versions. Now customers can experience the Rapid Preview models and bits for their dev & test purpose.
 

–> This rapid release includes some improvements and fixes to the new features added in SQL Server 2016 release, and as follows:

1. Stretch Database

2. Query Store

3. Temporal Data, added support for computed columns and Period columns with HIDDEN flag.

4. Columnstore Index, improved seek/scan performance
 

Check the [SQL Server blog] for all these updates in detail.


Time Travel with Temporal Tables in SQL Server 2016 – Part 2

June 17, 2015 2 comments

In my [previous post] I discussed about Temporal Data, how it will be supported in SQL Server and how to CREATE Temporal or System-Versioned Tables.
 

–> Here in this post we will see how we can enable System-Versioning in an existing table containing data. I will also tweak the data to show you the demo on how you can point to a time back in history and get details relative to that time. This will be like Time Travelling to past and seeing record details as if its current data, without much change to the current table’s data-structure.
 

1. Let’s create a sample table [dbo].[Employee] and populated it by JOINing some tables on [AdventureWorks] Database:

USE [TestManDB]
GO

;WITH CTE AS (
	SELECT 
		E.BusinessEntityID, P.FirstName, P.LastName, D.Name AS DepartmentName, 
		ROW_NUMBER() OVER(PARTITION BY E.BusinessEntityID ORDER BY D.ModifiedDate DESC) as RN

	FROM [AdventureWorks2014].[HumanResources].[Employee] E
	JOIN [AdventureWorks2014].[Person].[Person] P
	ON P.BusinessEntityID = E.BusinessEntityID
	JOIN [AdventureWorks2014].[HumanResources].[EmployeeDepartmentHistory] DH
	ON DH.BusinessEntityID = E.BusinessEntityID
	JOIN [AdventureWorks2014].[HumanResources].[Department] D
	ON D.DepartmentID = DH.DepartmentID
)
SELECT BusinessEntityID, FirstName, LastName, DepartmentName
	INTO dbo.Employee
FROM CTE
WHERE RN = 1
GO

 

2. The System Versioned Temporal table [dbo].[Employee] must have Primary Key defined: otherwise you will get the same error message (Msg 13553).

ALTER TABLE dbo.Employee 
	ADD CONSTRAINT PK_BusinessEntityID PRIMARY KEY (BusinessEntityID)
GO

 

3. Now to make [dbo].[Employee] table System Versioned we will add:

Two Audit columns of datetime2 datatype to store Start & End datetime.

– Use PERIOD FOR SYSTEM_TIME clause to associate these two columns as System Time.

ALTER TABLE dbo.Employee ADD 
	StartDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT CAST('1900-01-01' AS DATETIME2),
	EndDate	  datetime2 GENERATED ALWAYS AS ROW END   NOT NULL DEFAULT CAST('9999-12-31' AS DATETIME2),
PERIOD FOR SYSTEM_TIME (
	StartDate, 
	EndDate
)
GO

 

4. After all pre-requisites let’s enable the System-Versioning on [dbo].[Employee] table:

ALTER TABLE dbo.Employee 
	SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory))
GO

 

–> So, as soon you enable the System-Versioning the SQL Engine creates an another History table with similar schema and nests it under the main Temporal table, let’s check both the tables columns and contents:

SELECT TOP 10 * FROM dbo.Employee
SELECT TOP 10 * FROM dbo.EmployeeHistory
GO

SQL Server 2016 Temporal 08

You can see above the History table is empty as there are no updates on the Parent table. I’ve rounded the 5th row because I will update this row in next step for the demo.
 

5. Let’s make some updates on the parent Temporal Table (5th row): SQL Engine will automatically populate the History table.

UPDATE dbo.Employee 
SET FirstName = 'Gabriel'
WHERE BusinessEntityID = 5
GO

UPDATE dbo.Employee 
SET DepartmentName = 'Research and Development'
WHERE BusinessEntityID = 5
GO

UPDATE dbo.Employee 
SET DepartmentName = 'Executive'
WHERE BusinessEntityID = 5
GO

-- Let's check the records again:
SELECT * FROM dbo.Employee WHERE BusinessEntityID = 5
SELECT * FROM dbo.EmployeeHistory WHERE BusinessEntityID = 5
GO

You will see that after doing 3 UPDATEs on the parent Temporal Table the History table [dbo].[EmployeeHistory] is populated with 3 rows that contains the older versions on data in [dbo].[Employee] table across all columns.
 

6. Ok, now I’ll do some tweaks on the System Time column values of [dbo].[Employee] table.

– First of all I will switch OFF the System-Versioning on dbo.Employee table.

– Now I will update the date of System Time columns, set it to back in history (5-10 days back for an effective demo).

– Enable back the System-Versioning

ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF )
GO

update dbo.EmployeeHistory
set EndDate = '2015-06-01 18:47:07.5566710'
where BusinessEntityID = 5 AND EndDate = '2015-06-09 18:47:07.5566710'

update dbo.EmployeeHistory
set StartDate = '2015-06-01 18:47:07.5566710',
	EndDate = '2015-06-05 18:47:28.0153416'
where BusinessEntityID = 5 AND StartDate = '2015-06-09 18:47:07.5566710'

update dbo.EmployeeHistory
set StartDate = '2015-06-05 18:47:28.0153416'
where BusinessEntityID = 5 AND StartDate = '2015-06-09 18:47:28.0153416'
GO

ALTER TABLE [dbo].[Employee] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory))
GO

-- Check the data after datetime changes:
SELECT * FROM dbo.Employee WHERE BusinessEntityID = 5
SELECT * FROM dbo.EmployeeHistory WHERE BusinessEntityID = 5
GO

SQL Server 2016 Temporal 09
 

–> Ok, now here comes the most interesting part of the topic i.e. “Time Travel”:

SQL Server engine provides you FOR SYSTEM_TIME AS OF {datetime_value} option with your SELECT query to get details pointing to any time in the history, let’s check this here:

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2015-01-01'
WHERE BusinessEntityID = 5

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2015-06-03'
WHERE BusinessEntityID = 5

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2015-06-07'
WHERE BusinessEntityID = 5

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2015-06-10'
WHERE BusinessEntityID = 5
GO

The above four Queries will show you results from the History Table by pulling out the records for the particular date you mentioned by checking the date ranges in the Audit columns:

SQL Server 2016 Temporal 10
 

–> Let’s check the Execution Plan of the 4th SELECT statement:

SQL Server 2016 Temporal 11

So, you can see that you are just querying the dbo.Employee Temporal table, but SQL Engine is internally also querying the dbo.EmployeeHistory table and concatenating (UNION) the rows from both the operators.
 

–> Final Cleanup: before Dropping the Temporal & History tables, you need to switch OFF the System Versioning feature in the parent table.

ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[Employee]
GO
DROP TABLE [dbo].[EmployeeHistory]
GO

 

–> You can also check this demo in this video:
TemporalData


SQL Database Recovery tool to repair corrupt MDF file

June 16, 2015 1 comment

Database is a must have requirement of every type of Business systems, weather it is for customers, accounting or product. In order to create these databases the most common database system is Microsoft’s SQL Server. Due to its robustness and dynamic system facilities, it is a highly popular solution to manage a company’s business system.

A business database itself includes valuable data as it is a result of months hard work. What if this database becomes inaccessible due to multiple causes, and lead to a situation of data loss. Therefore, it is important to know about a reliable solution that can restore even the slightest part of database. Specialized SQL recovery tools are designed to perform SQL recoveries, therefore, they are highly in demands. Stellar Phoenix SQL Database Repair is one of a SQL database repair tool that gives the assurance of data recovery from damaged or corrupt SQL databases.

This review is about Stellar Phoenix SQL Database Repair product and its heights and short comings!

Disclaimer: This is not a paid review, and reflects my own experience while working with the product.
 

–> Testing Initiation

Stellar Phoenix SQL Database Repair is a popular product that claims to recover SQL database from all disaster scenarios. To check the higher promises of the product I have decided to check its accuracy and capabilities on one of my corrupt SQL server database, which is actually very large and highly important for me.
 

–> Installation Requirement Guide

Minimum system requirements for this recovery software are very normal as it needs A Pentium class processor, 1 GB of RAM, and 50 MB free disk space to be installed. This recovery tool is compatible with all the versions of SQL server, from MS SQL Server 7.0 to 2014, and all the versions of Windows operating systems, from Windows XP to 8 along with Windows Server 2003, 2008 and 2012.

When I have decided to test this product, I don’t need to setup a new system as all my system configurations fulfills all the requirements. One special quality I have noticed in this recovery application is; it automatically reconnects to the MS SQL server, in case of any disruption during the repair process to run the recovery smoothly.
 

–> Steps to select the database

Within few clicks you will be able to install the software on your machine. After successful installation, the main interface will show multiple menus and buttons to access various features of the software. Additionally, these various options can also be accessed through Quick Access Toolbar that is available at the top of the user interface, and can be customized accordingly.

Stellar Phoenix SQL Database Repair 1
 

In order to choose the database, you need to click on Select Database button and choose a MDF file that needs to be repaired and click ‘Repair’ to initiate the repair.

In case the location of your database file is unknown, Stellar Phoenix SQL Database Repair also avails you Find Database option that can find MDF file that you want to repair from selected drive and folder. After successful search, you will be able to see the list MS SQL Database files found in the selected drive or folder on the main window.

Stellar Phoenix SQL Database Repair 2
 

–> Initiate SQL database repair

Now, select the file from available list that needs to be repaired and select ‘Repair’ button to initiate repair. After successful completion, the left pane of windows displays list of repaired items along with Table, View, Synonyms, Defaults, Data Types, etc. in a tree format. The upper right pane displayed the content of selected items and the bottom pane displayed message log. You need to click on the item to preview the content of the listed items.

Stellar Phoenix SQL Database Repair 3
 

This software has a special feature that it counts all the records after successful repair. Although, this is an optional feature, but still worth for some users. To count my repaired records it took a good time, as the database contained a large number of items, it could be less for you if your database count is less.
 

–> Save Repaired items

To save the repaired files, just select the Save option available in the File menu. You could also finish this saving task by selecting ‘Save’ button from Quick Access Toolbar. The software asks you to Enter Server / Instance Name and choose a desired destination to save the repaired MDF file.

Now, select the Connect button to save the repaired MS SQL Database file. Make sure your SQL server is running during the repair process.
 

–> Additional Features to make the recovery task easier

I had a good experience with the software as it is easy to use and can quickly connect with the server. Here are some special features that will make your process much easier.
 

–> Find Specific Item from the list

After successful scan Stellar Phoenix SQL Database Repair allows you to find particular item(s) in tree view. You could use search bar available on the preview window along with two options and will give accuracy to your search.
 

–> Selective Recovery
This SQL database recovery tool can perform selective recovery of your database objects. You could choose to select specific objects that need to be recovered from the list and save them in your desired location.
 

–> Overall performance

Being developed by a reputed company (Stellar Data Recovery), Stellar Phoenix SQL Database Repair is genuinely a good software and after testing its complete modules I can say that it recovers all the objects of database along with multiple components, like Indexes, Views, Rules, User Defined Functions, Tables, Triggers, Stored Procedures, etc. It repaired my MDF and NDF files along with XML indexes and data types.
 

–> Sum up:

After using this product I can surely recommend this tool to SQL professionals who are looking for a tool that can help them in their disaster time. Although, the software is quite slow and will make you wait, but it will be worth when it displays the complete database which was inaccessible.


Temporal data support in SQL Server 2016 – Part 1

June 15, 2015 4 comments

This coming release of new SQL Server 2016 will also support for Temporal Data as a new feature called Temporal Tables or System-Versioned Tables.
 

A Temporal Data is tightly related to a Time period and used to handle the time-varying data. It provides the ability to look at data trends, types of data changes, and the overall data evolution within your database.
SQL Server 2016 Temporal 01

A Temporal Table is a table for which a PERIOD definition exists and which contains system columns with a datatype of datetime2 into which the period of validity is recorded by the system. The Temporal table has an associated History Table into which the system records all prior versions of each record with their period of validity.

With a Temporal Table, the value of each record at any point in time can be determined, rather than just the current value of each record.
 

The Temporal Data is closely related to Slowly Changing Dimensions (SCD) Type-6 method, but the whole mechanism is internally and automatically managed by the SQL Server DB Engine.
 

–> With Temporal Data support you can:

1. Time Travel back & forth and understand business trends over time.

2. Track and Audit all data changes.

3. Maintain a Slowly Changing Dimension (SCD) for decision support applications

4. Recover from accidental data changes by repairing record-level corruptions.

SQL Server 2016 Temporal 12
 

–> To create a Temporal Table following clauses are used with CREATE TABLE statement:

1. Two additional Start & End date Audit columns of datetime2 datatype for capturing the validity of records. You can use any meaningful column name here, we will use ValidFrom and ValidTo column names in our example below.

2. Both the column names have to be specified in and as PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) clause with the column list.

3. Specify WITH (SYSTEM_VERSIONING = ON) option at the end of the CREATE TABLE statement with optional (HISTORY_TABLE = {History_Table_Name}) option.
 

The above CREATE TABLE statement will create 2 tables:

1. one the parent Temporal or System-Versioned Table

2. and second the History Table
 

–> Now, as I mentioned in Step #3 above, you have an option to specify the History Table name or not. So let’s check both the options here:
 

–> Option #1: Create Temporal Table [dbo].[Department] with automatically named History table:

USE [TestManDB]
GO

CREATE TABLE dbo.Department 
(
    DepartmentID		int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
    DepartmentName		varchar(50) NOT NULL, 
    ManagerID			int NULL, 

    ValidFrom			datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
    ValidTo				datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,   

    PERIOD FOR SYSTEM_TIME (
		ValidFrom, 
		ValidTo
	)   
)
WITH ( SYSTEM_VERSIONING = ON ); -- No History table name given here
GO

– Check the History Table nested under the Temporal Table dbo.Department . The auto-naming convention goes like this MSSQL_TemporalHistoryFor_{parent_temporal_table_object_id}:

SQL Server 2016 Temporal 04

– Let’s Check the metadata of both the tables:

SELECT object_id, temporal_type, temporal_type_desc, history_table_id, name -- Department
FROM SYS.TABLES 
WHERE object_id = OBJECT_ID('dbo.Department', 'U')

SELECT object_id, temporal_type, temporal_type_desc, history_table_id, name -- MSSQL_TemporalHistoryFor_1397580017
FROM SYS.TABLES 
WHERE object_id = ( 
	SELECT history_table_id 
	FROM SYS.TABLES 
	WHERE object_id = OBJECT_ID('dbo.Department', 'U')
)
GO

– Output:

SQL Server 2016 Temporal 05

– To DROP both the Tables, first you need to switch OFF the System Versioning on the parent Temporal Table:

ALTER TABLE [dbo].[Department] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[Department]
GO
DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_1525580473]
GO

 

–> Option #2: Create Temporal Table [dbo].[Department] with a named History table [dbo].[DepartmentHistory]:

CREATE TABLE dbo.Department 
(
    DepartmentID		int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
    DepartmentName		varchar(50) NOT NULL, 
    ManagerID			int NULL, 

    ValidFrom			datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
    ValidTo				datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,   

    PERIOD FOR SYSTEM_TIME (
		ValidFrom, 
		ValidTo
	)   
)
WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory) );
GO

– Check the History Table dbo.DepartmentHistory nested under the parent Temporal Table dbo.Department:

SQL Server 2016 Temporal 06

– Let’s Check the metadata of both the tables:

SELECT object_id, temporal_type, temporal_type_desc, history_table_id, name 
FROM SYS.TABLES 
WHERE object_id = OBJECT_ID('dbo.Department', 'U')

SELECT object_id, temporal_type, temporal_type_desc, history_table_id, name  
FROM SYS.TABLES 
WHERE object_id = OBJECT_ID('dbo.DepartmentHistory', 'U')
GO

– Output:

SQL Server 2016 Temporal 07

– Final Cleanup, As mentioned above to DROP both the Tables, first you need to switch OFF the System Versioning on the parent Temporal Table by ALTER statement:

ALTER TABLE [dbo].[Department] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[Department]
GO
DROP TABLE [dbo].[DepartmentHistory]
GO

– Otherwise you will get following error message:

Msg 13552, Level 16, State 1, Line 82
Drop table operation failed on table ‘TestManDB.dbo.Department’ because it is not supported operation on system-versioned temporal tables.

 

–> Limitation of Temporal Tables:

1. Temporal querying over Linked Server is not supported.

2. History table cannot have constraints (PK, FK, Table or Column constraints).

3. INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.

4. TRUNCATE TABLE is not supported while SYSTEM_VERSIONING is ON

5. Direct modification of the data in a history table is not permitted.

6. INSTEAD OF triggers are not permitted on either the tables.

7. Usage of Replication technologies is limited.
 

–> You can also check this demo in this video:
TemporalData
 

In my [next post] we will see a DEMO of Temporal data in SQL Server and how you can Time-Travel with your data and get details about point in time history without any extra effort.


In-memory enhancements and improvements in SQL Server 2016


In-Memory tables were introduced in SQL Server 2014 and were also known as Hekaton tables. I’ve written previously about In-memory tables for SQL Server 2014 and you can check in my [previous posts] to know more about these type of tables with some Hands-on examples and demos.
 

–> In-memory tables as new concept in SQL Server 2014 had lot of limitations compared to normal tables. But with the new release of SQL Server 2016 some limitations are addressed and other features have been added for In-Memory tables. These improvements will enable scaling to larger databases and higher throughput in order to support bigger workloads. And compared to previous version of SQL Server it will be easier to migrate your applications to and leverage the benefits of In-Memory OLTP with SQL Server 2016.
 

–> I have collated all the major improvements here in the table below:

SQL Server 2016 - In Memory

* Collation Support
1. Non-BIN2 collations in index key columns
2. Non-Latin code pages for (var)char columns
3. Non-BIN2 collations for comparison and sorting in native modules
 

–> You can check more about In-Memory tables for SQL Server 2016 in MSDN BoL [here].


Follow

Get every new post delivered to your Inbox.

Join 450 other followers