Home » Articles posted by rohitmssqlfun
Author Archives: rohitmssqlfun
SQL Server 2016 – SQL Server Telemetry \ CEIP Services
CEIP (Customer Experience Improvement Program) is somethings runs from ages and almost each vendor perform it to collect product usage data, issues faced and other information. Vendor uses this information for market expansion, bug fixing and new features.
But all this happen with user confirmation during or after installation. Microsoft is also doing it for it’s multiple products.
As data becomes more important, telemetry for software is potentially a data leakage and part of security and audit concerns. This becomes more concerns when we comes to database systems.
With SQL Server 2016, Microsoft is installing SQL Server Telemetry \ CEIP Services by default (most of the users are unware that they have installed CEIP). You cannot skip the installation of these services.
SQL Server 2016 Telemetry \ CEIP Services – You can see 3 services per instance separately for DB engine, SSAS & SSIS features installed.
SQLTELEMETRY$instancename
SSASTELEMETRY$instancename
SSISTELEMETRY130$instancename
How to disable SQL Server 2016 Telemetry \ CEIP Services?
NOTE : DO NOT REMOVE THESE SERVICES, AS THIS CAN IMPACT SQL SERVER SERVICE PACK INSTALLATION IN FUTURE.
- Stop and disable all SQL Server Telemetry available on your machine. No. of services can differ on basis of features or no. Of instances installed.
You can use PowerShell script to check SQL Telemetry services status.
Get-Service |? name -Like "SQLTELEMETRY*" | select -property name,starttype,status
Get-Service |? name -Like "SSASTELEMETRY*" | select -property name,starttype,status
Get-Service |? name -Like "SSISTELEMETRY*" | select -property name,starttype,status
- Disable CEIP registry keys to 0
Two parameters have to be set to 0:
- CustomerFeedback
- EnableErrorReporting
- The first registry key is HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\130\
- The second registry key is HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\130\
- The other registry keys are per instance and per services(Engine, SSAS and SSRS):
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL**.<instance>\CPE\
- HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.<instance>\CPE\
- HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.<instance>\CPE\
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
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