How to create System-versioned temporal tables? – New database feature of SQL Server 2016
Creating a System-Versioned Temporal Table – There are three ways to create a system-versioned temporal table with regards to how the history table is specified:
1) Creating a temporal table with an anonymous history table
Creating a temporal table with an anonymous name. This is easiest way to create it when you do not concern about history table name. In the example below, a new table is created with system-versioning enabled and name is automatically decide by SQL Server.
· A system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END
· The PERIOD columns are always assumed to be non-nullable, even if nullability is not specified. If the PERIOD columns are explicitly defined as nullable, the CREATE TABLE statement will fail.
· The history table must always be schema-aligned with the current or temporal table, in terms of number of columns, column names, ordering and data types.
· An anonymous history table is automatically created in the same schema as current or temporal table.
· The anonymous history table name has the following format: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffix]. Suffix is optional and it will be added only if the first part of the table name is not unique.
· The history table is created as a rowstore table. PAGE compression is applied if possible, otherwise the history table will be uncompressed. For example, some table configurations, such as SPARSE columns, do not allow compression.
· A default clustered index is created for the history table with an auto-generated name in format IX_<history_table_name>. The clustered index contains the PERIOD columns (end, start).
2) Creating a temporal table with a default history table
Creating a temporal table with a default history table is a convenient option when you want to control naming and still rely on the system to create the history table with the default configuration. In the example below, a new table is created with system-versioning enabled with the name of the history table explicitly defined.
· The schema name is mandatory for the HISTORY_TABLE parameter.
· If the specified schema does not exist, the CREATE TABLE statement will fail.
· If the table specified by the HISTORY_TABLE parameter already exists, it will be validated against the newly created temporal table in terms of schema consistency and temporal data consistency. If you specify an invalid history table, the CREATE TABLE statement will fail.
Msg 2714, Level 16, State 6, Line 1
There is already an object named ‘DEPARTMENTHISTORY_ALREADYTHERE’ in the database.
Msg 13523, Level 16, State 1, Line 3
Setting SYSTEM_VERSIONING to ON failed because table ‘SQL2016_FeatureTest.dbo.Department’ has 6 columns and table ‘SQL2016_FeatureTest.dbo.DepartmentHistory_AlreadyThere’ has 1 columns.
3) Creating a temporal table with a user-defined history table
Creating a temporal table with user-defined history table is a convenient option when the user wants to specify a history table with specific storage options and additional indexes. In the example below, a user-defined history table is created with a schema that is aligned with the temporal table that will be created. To this user-defined history table, a clustered columnstore index and additional non clustered rowstore (B-tree) index is created for point lookups. After this user-defined history table is created, the system-versioned temporal table is created specifying the user-defined history table as the default history table.
| Action | Temporal table with an anonymous history table | Temporal table with a default history table | Temporal table with a user-defined history table |
| Create Table with Temporal Table | CREATE TABLE Department_anonymous
( DeptID int NOT NULL PRIMARY KEY CLUSTERED , DeptName varchar(50) NOT NULL , ManagerID INT NULL , ParentDeptID int NULL , 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)
|
CREATE TABLE Department_WithHistoryTable
( DeptID int NOT NULL PRIMARY KEY CLUSTERED , DeptName varchar(50) NOT NULL , ManagerID INT NULL , ParentDeptID int NULL , 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.DepartmentHistory) ) ;
|
CREATE TABLE DepartmentHistory_UserDefined
( DeptID int NOT NULL , DeptName varchar(50) NOT NULL , ManagerID INT NULL , ParentDeptID int NULL , SysStartTime datetime2 NOT NULL , SysEndTime datetime2 NOT NULL ); GO CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory_UserDefined; GO CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS ON DepartmentHistory_UserDefined (SysEndTime, SysStartTime, DeptID); GO CREATE TABLE Department_UserDefined ( DeptID int NOT NULL PRIMARY KEY CLUSTERED , DeptName varchar(50) NOT NULL , ManagerID INT NULL , ParentDeptID int NULL , 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.DepartmentHistory_UserDefined)) ; |
| Temporal Table is created along with main table: |  |
 |
 |
| Insert data into temporal table : | INSERT INTO DEPARTMENT_ANONYMOUS(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID)
VALUES (111,’FINANCE’,502,101) INSERT INTO DEPARTMENT_ANONYMOUS(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (117,’WALFARE’,522,111) UPDATE A SET A.MANAGERID= 557 FROM DEPARTMENT_ANONYMOUS A WHERE A.DEPTID =117
|
INSERT INTO DEPARTMENT_WITHHISTORYTABLE(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID)
VALUES (111,’FINANCE’,502,101) INSERT INTO DEPARTMENT_WITHHISTORYTABLE(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (117,’WALFARE’,522,111) UPDATE A SET A.MANAGERID= 557 FROM DEPARTMENT_WITHHISTORYTABLE A WHERE A.DEPTID =117
|
INSERT INTO Department_UserDefined(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID)
VALUES (111,’FINANCE’,502,101) INSERT INTO Department_UserDefined(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (117,’WALFARE’,522,111) UPDATE A SET A.MANAGERID= 557 FROM Department_UserDefined A WHERE A.DEPTID =117
|
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.sqlservercentral.com/blogs/mssqlfun/
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
Kronothon2.0 – Accept the challenge
Kronothon2.0 – The only difference between Success and Failure is one’s attitude.
Register here to win cash prizes up to 5 Lakhs – http://kronos.hackerearth.com/kronothon-2/
#Work4Kronos #MyWorkInspired #Kronothon2.0
#Coding #Ideas #Win #Engineer #MBA #Register #Hackerearth #codathon
#casestudy #Ideathon #hackerearth #Challenge #Ilovecoding #excitement#Innovation
Introduction to System-versioned temporal tables – New database feature of SQL Server 2016
System-versioned temporal tables is new database feature of SQL Server 2016. Another improved version of table level auditing with some new features after trigger, CT & CDC. System-versioned temporal tables stores data of table data modification history. Temporal is a database feature that was introduced in ANSI SQL 2011.
Most common usage for temporal tables are:
- Data Auditing
- Repairing or recovering record level corruptions
- Recovering from missing records
- Trend Analysis
How System-versioned temporal tables works? –
System-versioned temporal table contains actual data and corresponding History table contains old data. When you perform any DML operation on table, Old version of data got moved to history table.
How to create System-versioned temporal tables? –
We have 3 possible ways to System-versioned temporal tables.
1. Creating a temporal table with an anonymous history table
2. Creating a temporal table with a default history table
3. Creating a temporal table with a user-defined history table
How to stop System-versioning and drop actual & history table? –
To stop system versioning and drop actual data and history table, we need to off system versioning on table before drop table execution.
| USE <DB_Name>
GO ALTER TABLE [dbo].<Table_Name> SET ( SYSTEM_VERSIONING = OFF ) GO DROP TABLE [dbo].<Table_Name> GO DROP TABLE [dbo].<Table_Name_History_Table> GO |
How to query System-versioned temporal tables? –
You can perform normal select statement on both actual and history table like other normal tables. But System-versioned temporal tables got new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables. This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.
| Select * from Department_UserDefined
Select * from DepartmentHistory_UserDefined select * from Department_UserDefined FOR SYSTEM_TIME AS OF ‘2018-01-21 09:34:44.4731356’ |
5 Clauses FOR SYSTEM_TIME:-
| Expression | Qualifying Rows | Description |
| AS OF<date_time> | SysStartTime <= date_time AND SysEndTime > date_time | Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <date_time> parameter. |
| FROM<start_date_time>TO<end_date_time> | SysStartTime < end_date_time AND SysEndTime > start_date_time | Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM argument or ceased being active after the <end_date_time>parameter value for the TO argument. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. Rows that ceased being active exactly on the lower boundary defined by the FROM endpoint are not included and records that became active exactly on the upper boundary defined by the TO endpoint are not included also. |
| BETWEEN<start_date_time>AND<end_date_time> | SysStartTime <= end_date_time AND SysEndTime > start_date_time | Same as above in the FOR SYSTEM_TIME FROM <start_date_time>TO<end_date_time> description, except the table of rows returned includes rows that became active on the upper boundary defined by the <end_date_time> endpoint. |
| CONTAINED IN (<start_date_time> , <end_date_time>) | SysStartTime >= start_date_time AND SysEndTime <= end_date_time | Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included. |
| ALL | All rows | Returns the union of rows that belong to the current and the history table. |
How to check table type? –
SYS.TABLES system view got new columns to check table is SYSTEM_VERSIONED_TEMPORAL_TABLE or HISTORY_TABLE or NON_TEMPORAL_TABLE. You can check the list of all SYSTEM_VERSIONED_TEMPORAL_TABLE & HISTORY_TABLE from SYS.Tables.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.sqlservercentral.com/blogs/mssqlfun/
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
SQL Server 2016 – How to use Dynamic Data Masking (DDM)?
Dynamic Data Masking (DDM) is new feature of SQL Server 2016. DDM helps in preventing unauthorized access to sensitive data. DDM enables only privilege users to have complete data. Un-privilege users will have access of MASKED data only.
As an example, a call center support person may identify callers by several digits of their social security number or credit card number, but those data items should not be fully exposed to the support person. A masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query. For another example, by using the appropriate data mask to protect personally identifiable information (PII) data, a developer can query production environments for troubleshooting purposes without violating compliance regulations.
Benefits
1. Limited data access increase data security and reduce possibilities of unauthorized data access
2. No change at Application code.
3. Current complex logic in application to MASK sensitive data is no more needed. It greatly simplify the design and coding of security in your application.
4. Data is not changed at storage, It MASKED at runtime on basis of masked function used.
Limitations and Restrictions
1) A masking rule cannot be defined for the following column types:
· Encrypted columns (Always Encrypted)
· FILESTREAM
· COLUMN_SET or a sparse column that is part of a column set.
· Computed column
· A column with data masking cannot be a key for a FULLTEXT index.
2) Backup taken by user without UNMASKED permission, will have UNMASKED data. This is because data masking is dynamically done at time of data view. To secure backups, We have TDE or backup encryption.
3) Data Export done by user without UNMASKED permission will result MASKED data to be exported.
4) Use of SELECT INTO or INSERT INTO by user with UNMAKED permission to copy data from a masked column into another table will copy MASKED data in new table.
Permissions
1. No additional permission needed to create table with DDM columns. Only the standard CREATE TABLE and ALTER on schema permissions will be enough.
2. To Adding, replacing, or removing the mask of a column, requires the ALTER ANY MASK permission and ALTER permission on the table.
3. User with super permissions (like sysadmin, DB_Owner etc.) or UNMASK permission can view the unmaked data.
We have 4 functions provided by Microsoft to create dynamic masked columns for different data types and purpose.
| Function | Description |
| Default | Full masking according to the data types of the designated fields. |
| For string data types, use XXXX or fewer Xs if the size of the field is less than 4 characters (char, nchar, varchar, nvarchar, text, ntext). | |
| For numeric data types use a zero value (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, and real). | |
| For date and time data types use 01.01.1900 00:00:00.0000000 (date, datetime2, datetime, datetimeoffset, smalldatetime, time). | |
| For binary data types use a single byte of ASCII value 0 (binary, varbinary, image). | |
| Masking method which exposes the first letter of an email address and the constant suffix “.com”, in the form of an email address. . aXXX | |
| Random | A random masking function for use on any numeric type to mask the original value with a random value within a specified range. |
| Custom String | Masking method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix |
| Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed. |
How to create table with DDM columns?
| CREATE TABLE EMPINFO_DDM
( EMPID INT IDENTITY(1,1), FIRSTNAME NVARCHAR(50) NOT NULL, SALART INT, EMAIL VARCHAR(100), JOINDATE DATETIME, FNAME_DEFAULT NVARCHAR(50) MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL, SALARY_DEFAULT INT MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL, EMAIL_DEFAULT NVARCHAR(50) MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL, JOINDATE_DEFAULT DATETIME MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL, FNAME_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL, SALARY_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL, EMAIL_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL, JOINDATE_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL, SALARY_RANDOM INT MASKED WITH (FUNCTION = ‘RANDOM(999, 9999)’) NOT NULL, FNAME_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(0,”XXX-XXXX-XXXX-“,4)’) NOT NULL, SALARY_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(1,”XXXXXXX”,1)’) NOT NULL, EMAIL_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(0,”X-X-X”,14)’) NOT NULL, JOINDATE_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(1,”XXXXXXX”,1)’) NOT NULL ) |
How to check if any columns is masked in table?
| SELECT NAME COLUMNNAME, IS_MASKED FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = ‘EMPINFO_DDM’ |
How to get masked columns & used masked functions details?
| SELECT C.NAME, TBL.NAME AS TABLE_NAME, C.IS_MASKED, C.MASKING_FUNCTION
FROM SYS.MASKED_COLUMNS AS C JOIN SYS.TABLES AS TBL ON C.[OBJECT_ID] = TBL.[OBJECT_ID] WHERE IS_MASKED = 1; |
How to check table with DDM column data?
You can read table data using simple SELECT command, No change in select command needed.
1) Data view by User with super permissions
2) Data view by User having only select permission on table
What happen if USER with limited access used “SELECT INTO or INSERT INTO to copy data from a masked column into another table “?
If User with limited access use SELECT INTO or INSERT INTO to copy data from a masked column into another table. New table will get masked data as well.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.sqlservercentral.com/blogs/mssqlfun/
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
