Temporal Tables are generally available in Azure SQL Database

Temporal Tables allow you to track the full history of data changes directly in Azure SQL Database, without the need for custom coding. With Temporal Tables you can see your data as of any point in time in the past and use declarative cleanup policy to control retention for the historical data.

When to use Temporal Tables?

Quite often you may be in the situation to ask yourself fundamental questions: How did important information look yesterday, a month ago, a year ago, etc. What changes have been made since the beginning of the year? What were the dominant trends during a specific period of time?  Without proper support in the database, however, questions like these have never been easy to answer.
Temporal Tables are designed to improve your productivity when you develop applications that work with ever-changing data and when you want to derive important insights from the changes.
Use Temporal Tables to:

  1. Support data auditing in your applications
  2. Analyze trends or detect anomalies over time
  3. Easily implement slowly changing dimension pattern
  4. Perform fine-grained row repairs in case of accidental data errors made by humans or applications

Manage historical data with easy-to-use retention policy

Keeping history of changes tends to increase database size, especially if historical data is retained for a longer period of time. Hence, retention policy for historical data is an important aspect of planning and managing the lifecycle of every temporal table.  Temporal Tables in Azure SQL Database come with an extremely easy-to-use retention mechanism. Applying retention policy is very simple: it requires users to set single parameter during the table creation or table schema change, like shown in the following example.

ALTER TABLE [WebSiteClicks]
SET 
(
	SYSTEM_VERSIONING = ON 
	(
		HISTORY_TABLE = dbo. WebSiteClicks_History, 
                HISTORY_RETENTION_PERIOD = 3 MONTHS  
	)
);

You can alter retention policy at any moment and your change will be effective immediately.

Why you should consider Temporal Tables?

If you have requirements for tracking data changes, using Temporal Tables will give you multiple benefits over any custom solution. Temporal Tables will simplify every phase in the development lifecycle: object creation, schema evolution, data modification, point-in-time analysis and data aging.

temporaltableinazure

 

Temporal Tables are generally available in Azure SQL Database

CREATE DATABASE (Azure SQL Database)

Creates a new database. You must be connected to the master database to create a new database.

CREATE DATABASE database_name  
    AS COPY OF [source_server_name.] source_database_name  
    [ SERVICE_OBJECTIVE =   
          {  'basic' | 'S0' | 'S1' | 'S2' | 'S3'   
            | 'P1' | 'P2' | 'P3' | 'P4'| 'P6' | 'P11'   
            | { ELASTIC_POOL(name = <elastic_pool_name>) } }   
    ]  
[;]

Copying a database using the CREATE DATABASE statement is an asynchronous operation. Therefore, a connection to the SQL Database server is not needed for the full duration of the copy process. The CREATE DATABASE statement will return control to the user after the entry in sys.databases is created but before the database copy operation is complete. In other words, the CREATE DATABASE statement returns successfully when the database copy is still in progress.

Continue reading “CREATE DATABASE (Azure SQL Database)”

CREATE DATABASE (Azure SQL Database)

How do you get all ancestors of a node using SQL Server hierarchyid

Given a table with a hierarchyid type column, how do you write a query to return all rows that are ancestors of a specific node?

There is an IsDescendantOf() function, which is perfect for getting the children, but there’s no corresponding IsAncestorOf() function to return ancestors.

Continue reading “How do you get all ancestors of a node using SQL Server hierarchyid”

How do you get all ancestors of a node using SQL Server hierarchyid

Concatenate All Column Values in Sql

If you work with the SQL you might have came across this question sometime. How to Concatenate all column values from different rows returned from a SQL query into one value?

This is an example:

a query returns:

FOO
------
RES1

RES2

RES3

now we want to have a result like the following one:

FOOCONCAT
-----
RES1RES2RES3

Continue reading “Concatenate All Column Values in Sql”

Concatenate All Column Values in Sql