New Features of SQL Server 2016
SQL Server 2016 – Part 1
Getting Started with Stretch Database Functionality in SQL Server 2016 - Part 2
Row Level Security with SQL Server 2016: Part 1 – Allow Access to Only a Subset of Rows Using Row Level Security
Row Level Security with SQL Server 2016: Part 2 - Blocking Updates at the Row Level
Altering an Existing Table to Support Temporal Data
Getting Started with JSON Support in SQL Server 2016 – Part 2
Getting Started with Temporal Table in SQL Server 2016 – Part 2
Getting Started with Temporal Table in SQL Server 2016 – Part 1
SQL Server 2016 internally creates a history table with the same schema as your current table when you don’t specify a name for the specific history table.
If you already have an existing table that you want to use it as history table, you can specify its name during table creation or modification,
however you need to ensure it has the same schema as the current table and it meets other requirements as discussed earlier.
CREATE TABLE Employee
(
[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
, [Name] nvarchar(100) NOT NULL
, [Position] varchar(100) NOT NULL
, [Department] varchar(100) NOT NULL
, [AnnualSalary] decimal (10,2) NOT NULL
, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
, [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
INSERT INTO Employee ([EmployeeID], [Name], [Position], [Department], [AnnualSalary])
VALUES
(8,'Hari','TA', 'IT', 45000)
,(2,'Manpreet','SE', 'IT', 25000)
,(3,'Sandy','SE', 'IT', 43000)
,(4,'Mandeep','TA', 'IT', 53000)
,(5,'Sandeep','TL', 'IT', 66000)
,(6,'Marlon','PM', 'IT', 79000)
SELECT * FROM Employee
SELECT * FROM EmployeeHistory
UPDATE Employee
SET [AnnualSalary] = 53000
WHERE EmployeeID = 2
GO
UPDATE Employee
SET [Department] = 'TESTING'
WHERE EmployeeID = 8
DELETE FROM Employee WHERE EmployeeID = 3
GO
SELECT * FROM Employee
GO
SELECT * FROM EmployeeHistory
GO
/*
---// If you wish to Drop newly created temporal table
--// First disable the system versioning
ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF)
GO
--// Drop Period definition.
--// This will allow manipulations on the PERIOD columns
ALTER TABLE dbo.Employee
DROP PERIOD FOR SYSTEM_TIME
GO
--Now drop Employee and EmployeeHistory tables
--(To drop these tables, dropping PERIOD definition is optional)
DROP TABLE Employee
DROP TABLE EmployeeHistory
GO
*/
DECLARE @st AS VARCHAR(500) = '24,47,944,44,,99'
SELECT [value] FROM STRING_SPLIT ( @st , ',' )
DECLARE @str1 VARCHAR(35) = 'ABCDEFGH'
SELECT @str1, STUFF(@str1,4,5,'_STUFF')
DECLARE @str VARCHAR(35) = 'ABCDEFGH'
SELECT @str, REPLACE(@str,'DEFGH','_REPLACE')
