What built in mechanism does SQL Server have to do Flashback Queries?

Question

Think that says it all?

Answer 1

None. SQL Server does not have an equivalent feature.

---

UPDATE: From SQL Server 2016 on, this information is outdated. See the comments and answers below.

Answer 2

I know this question is quite old, but with SQL Server 2016, Temporal Tables is a feature: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

Maybe it can help others in case they come to this topic (Searching for something similar to Oracle Flashback feature)

With temporal tables enabled, you can query table AS OF a specific timestamp and retrieve rows as they were in that specific timestamp, just like you were used to do in Oracle:

(SELECT * FROM EMPLOYEE AS OF TIMESTAMP ('13-SEP-04 8:50:58','DD-MON-YY HH24: MI: SS')

Equivalent query in SQL Server for a table with SYSTEM_VERSIONING=ON will be:

SELECT * FROM EMPLOYEE FOR SYSTEM_TIME AS OF '2004-09-01 08:50:58'

To enable SYSTEM_VERSIONING for an existing table with rows you may use the following script:

ALTER TABLE [dbo].[TABLE] ADD [SysStartTime] datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT DF_Inventory_SysStartTime DEFAULT '1900-01-01 00:00:00', [SysEndTime] datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT DF_Inventory_SysEndTime DEFAULT '9999-12-31 23:59:59', PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])  

ALTER TABLE [dbo].[TABLE] SET (SYSTEM_VERSIONING = ON); 

After enabling SYSTEM_VERSIONING, the History table will show under the table where you enabled versioning:

To Remove SYSTEM_VERSIONING from a table:

ALTER TABLE [dbo].[TABLE] SET (SYSTEM_VERSIONING = OFF);  

ALTER TABLE [dbo].[TABLE] DROP PERIOD FOR SYSTEM_TIME;  

ALTER TABLE [dbo].[TABLE] DROP COLUMN [SysStartTime], [SysEndTime]; 

For more info you can visit the following link (or official Microsoft documentation referenced before): http://www.sqlservercentral.com/articles/SQL+Server+2016/147087/