Monthly Archives: June 2016

SQL 2016 Temporal Tables

— By Lori Brown @SQLSupahStah

A temporal table is a table that holds old versions of rows from a base table. In other words it is a history table.  By having temporal tables SQL Server can automatically manage moving old row versions to the temporal table every time a row in the base table is updated.  The temporal table is physically a different table then the base table, but is linked to the base table.

If you have ever needed to make business decisions based on insights from data that has evolved, you might need temporal tables.

Reasons to use temporal tables are…

  • Auditing data changes and performing data forensics
  • Reconstructing state of the data as of any time in the past
  • Calculating trends over time
  • Maintaining a slowly changing dimension for decision support applications
  • Recovering from accidental data changes and application errors

Below I have created a base temporal table, have designated the history table and have set system-versioning on with data broken up by the RecValidFrom & RecValidTo columns. These columns are equivalent to a start time and end time to the history table and are used to define the period that the record is valid.

CREATE TABLE dbo.EmpTemporal

(ID INT PRIMARY KEY

,FirstName VARCHAR(30)

,LastName VARCHAR(50)

,Salary INT

,City VARCHAR(30)

,RecValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL

,RecValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL

,PERIOD FOR SYSTEM_TIME (RecValidFrom,RecValidTo)) WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmpTemporalHistory))

GO

Blog_20160630_1

Let’s add some records and see that they look like in the base table.

INSERT INTO dbo.EmpTemporal(ID, FirstName, LastName, Salary)

VALUES (1, ‘Lori’, ‘Brown’, 50000)

,(2, ‘Lucy’, ‘Lu’, 50000)

,(3, ‘Joe’, ‘Blow’, 50000)

,(4, ‘Bob’, ‘Barker’, 50000)

,(5, ‘Adam’, ‘West’, 50000)

GO

 

SELECT * FROM dbo.EmpTemporal

GO

Blog_20160630_2

Now let’s make changes to the base table so that we can see how data changes over time…

UPDATE dbo.EmpTemporal     — Lucy’s last name is changed

SET LastName = ‘Stewart’

WHERE ID = 2

 

UPDATE dbo.EmpTemporal     — Lori got a raise

SET Salary = 55000

WHERE ID = 1

 

DELETE dbo.EmpTemporal     — Bob Barker quit and was deleted

WHERE ID = 4

 

UPDATE dbo.EmpTemporal     — Adam moved      

SET City = ‘West Memphis’

WHERE ID = 5

 

UPDATE dbo.EmpTemporal    — Lucy also got a raise

SET Salary = 55000

WHERE ID = 2

We can query data in either the base table or the history table or from both using the FOR SYSTEM_TIME function with a valid time range to filter on.

SELECT * FROM dbo.EmpTemporal

Blog_20160630_3

SELECT * FROM dbo.EmpTemporalHistory

Blog_20160630_4

SELECT * FROM dbo.EmpTemporal

FOR SYSTEM_TIME

BETWEEN ‘2016-01-01 00:00:00.0000000’ AND ‘2016-04-01 00:00:00.0000000’

WHERE ID = 2

ORDER BY RecValidFrom

Blog_20160630_5

Limitations:

  • Temporal tables must have a primary key.
  • History table cannot have constraints (primary key, foreign key, table or column constraints).
  • INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.
  • TRUNCATE TABLE is not supported while SYSTEM_VERSIONING is ON. You can disable by simply turning SYSTEM_VERSIONING = OFF.

ALTER TABLE dbo.EmpTemporal SET (SYSTEM_VERSIONING = OFF)

  • Direct modification of the data in a history table is not permitted.
  • ON DELETE CASCADE and ON UPDATE CASCADE are not permitted on the current table.
  • Usage of replication technologies is limited.

More limitations can be found here…. https://msdn.microsoft.com/en-us/library/mt604468.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

SQL 2016 COMPRESS and DECOMPRESS Functions

–By Lori Brown @SQLSupahStah

SQL Server 2016 has introduced a couple of new functions that can be very useful and save storage space to boot. COMPRESS and DECOMPRESS offers the benefit of compressing column data thereby saving storage space. COMPRESS uses the GZIP compression algorithm and actually compresses data before it is stored in a column or variable and returns binary data. DECOMPRESS combined with CAST reverses the COMPRESS function returns the decompressed version of any compressed data.

I’ll walk us through a simple example of using the new COMPRESS and DECOMPRESS functions using the AdventureWorks database. I first created both a compressed and uncompressed table to hold the exact same data so I can demonstrate the space savings using COMPRESS. Then, I loaded both tables with data from the person.person table in AdventureWorks.

 

CREATE TABLE PersonsCompressed (

FirstName nvarchar(50),

LastName nvarchar(50),

CompressedInfo varbinary(max))

GO

 

CREATE TABLE PersonsUNCompressed (

FirstName nvarchar(50),

LastName nvarchar(50),

UnCompressedInfo nvarchar(max))

GO

 

I made up a bunch of different length text values to put into the 3rd column in each table but here are my basic insert statements. I ran the insert statements many times until I had a significant amount of data in each table.

 

— Load table with compressed data using Person.Person table in AdventureWorks

INSERT INTO PersonsCompressed (FirstName, LastName, CompressedInfo)

SELECT FirstName, LastName, COMPRESS(‘What a nice person they are!….’) FROM Person.Person

INSERT INTO PersonsCompressed (FirstName, LastName, CompressedInfo)

SELECT FirstName, LastName, COMPRESS(‘highly optimistic person who likes to laugh a lot. Goofy. Intrigued by anything science and love intellectual conversations. Thinks on the logical side of things…..’) FROM Person.Person

GO

 

— Load table with UNcompressed data using Person.Person table in AdventureWorks

INSERT INTO PersonsUnCompressed (FirstName, LastName, UnCompressedInfo)

SELECT FirstName, LastName, ‘What a nice person they are!….’ FROM Person.Person

INSERT INTO PersonsUNCompressed (FirstName, LastName, UnCompressedInfo)

SELECT FirstName, LastName, ‘highly optimistic person who likes to laugh a lot. Goofy.

Intrigued by anything science and love intellectual conversations. Thinks on the logical side of things…..’ FROM Person.Person

GO

 

After loading up my tables with a lot of records that are exactly the same except one has a compressed column this is what I see when I check how much space each table is taking:

Blog_20160616_1

That is a space savings of over 60% which can be significant in larger tables!

If you query the tables you can see that the CompressedInfo column is not displayed as the text value that was put in since it was compressed and stored as binary data.

Blog_20160616_2

We have to DECOMPRESS the CompressedInfo column and CAST it to the correct data type to read it.

Blog_20160616_3

There are negatives to the new functions. While MSDN states that compressed columns cannot be indexed (https://msdn.microsoft.com/en-us/library/mt622775.aspx ), I was able to add the column as an included column in an index. And you will likely have to change existing code to use the new functions.

So, next I want to know how performance is affected when having to compress and decompress data in queries. I found that there is considerably more Duration and CPU used when using DECOMPRESS in a query. That seems logical to me since the CPU is where I would expect the work of decompressing data to happen.

Blog_20160616_4

Interestingly, when using COMPRESS and inserting records it was basically the same as inserting without compressing the data.

Blog_20160616_5

A good scenario to use compressed columns would be to use them for archived data with text columns or data in tables that are infrequently accessed.

Hopefully, my small examples will give you some valuable insight into what to expect if you want to use the new functions. Enjoy!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

SQL 2016 SSMS Download Location

Now that SQL Server 2016 has been released (https://blog.sqlrx.com/2016/06/01/sql-server-2016-rtm-available/ ), DBA’s are going to notice that the Management Studio tools are not in the list of options to install. Starting in SQL 2016, SSMS is a separate download and install. You can find the download at: https://msdn.microsoft.com/library/mt238290.aspx

The install notes say that it can be installed side-by-side with other versions of SSMS. The look is a little bit different but all is essentially the same.

Blog_20160609_1

There are new and improved features such as Live Query Statistics and the ability to edit top 200 rows in a table without SSMS crashing like it used to.

Blog_20160609_2

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

SQL Server 2016 RTM Available!!

In case you live under a rock or were at the dentist, Microsoft announced that SQL Server 2016 has been made generally available today June 1, 2016. See: https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/01/sql-server-2016-is-generally-available-today/

Pricing is as follows:

Blog_20160601_1

A handy feature comparison of previous versions of SQL can be located here:

https://www.microsoft.com/en-us/server-cloud/products/sql-server/comparison.aspx

SQL 2016 feature comparison between editions can be found here:

https://msdn.microsoft.com/en-us/library/cc645993.aspx

Let the upgrades begin!! Everything upgrade related can be found here: https://msdn.microsoft.com/library/bb677622.aspx

We are excited to be working with this new version! Give us a shout with questions and stories of your SQL 2016 experiences!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!