Sql server when was a table created

So, we can see that it has the same columns and data in the result set:. At first glance, TableA and TestTable seem identical, but when we compare their structures in a more detailed way, we will find several differences.

In the image below from SSMS table properties we can see what is different:. After carefully looking at their structures, we can conclude that neither indexes, primary key or check constraints of TestTable are transferred to TableA. Sometimes it is needed to copy only some of the data from the source into the target table. What if we just need an empty copy of a specific table? As a result, an empty table - TableC is created from TestTable. Like in the previous examples, the column names, identity specification, and the nullability of the columns are inherited from the base table:.

It is also possible to achieve the same result by using another statement. The last clause is the ALL clause that performs an internal union between the rows in the main temporal table and the history table without specifying any time range as follows:.

The sys. The new system catalog view sys. Full information can be retrieved by joining the sys. Assume that we need to tune the performance of the below query that is running very frequent in our system and any enhancement in it will make a difference in the overall performance:.

The execution plan of the query shows us that a Clustered Index Scan is performed in the temporal table as there is no index on the TimeStart and TimeEnd columns:. As you can see, the index that we created in the temporal table enhanced the query performance and decreased the execution time considerably.

Depending on the workload on your environment, you can customize the best index that fit your queries and enhance your system performance.

The System-Versioned Temporal table is a new type of table that is linked with a related history table in order to keep a history for each row change performed on that table.

You can develop your own solution depending on the temporal table to track the data changes with the change time and rollback any disaster changes without the need to restore the backup files.

You can get information about the temporal tables and the period columns using the existing and the newly introduced SQL Server system objects. The good point here is that it is managed only by the system requiring no effort from your side to define retention periods and follow up the insertion or deletion processes on the historical tables. As any new feature, test it carefully in your test environment before applying it to the live environment. FROM sys.

Author Recent Posts. Ahmad Yaseen. To find the creation date and the date of the last modification of triggers, query the sys.

Find the creation date and the date of the most recent modification of triggers. Your participation helps us to help others. By visiting this site, users agree to our disclaimer. The members, admins, and authors of this website respect your privacy. All logos and trademarks in this site are property of their respective owner. Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming.