SQL 2016 Temporal Tables

Another interesting SQL 2016 feature is temporal tables. When I first had this concept explained to me I thought they had a lot in common with Slowly Changing Dimensions, and they do. There is already support for temporal tracking in PostgreSQL and Oracle, and SQL 2016 has it now as of CTP 2.2. Here is some stuff I did just now using this new feature:

CREATE DATABASE BackToTheFuture
USE BackToTheFuture

CREATE TABLE BackToTheFuture
(
ID int NOT NULL PRIMARY KEY CLUSTERED,
FavoriteColor varchar(50) NOT NULL,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

INSERT INTO BackToTheFuture VALUES (1, 'Green')
UPDATE BackToTheFuture SET FavoriteColor = 'Blue' WHERE ID = 1

--unfortunately these times are going to have to be hard coded when this demo runs again
--I tried and failed to make the dates dynamic

SELECT * FROM BackToTheFuture --ok so its just blue
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/11/15 22:16' --nothing I hadnt inserted yet
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/11/15 22:17' --wait, what?
SELECT *, SysStartTime, SysEndTime FROM BackToTheFuture --oh look time is in GMT
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/12/15 02:14' --when I still liked green
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/12/15 02:15' --after I started liking blue
USE master
DROP DATABASE BackToTheFuture