Merge into a temporal table

SQL Server 2016 supports Temporal Tables (aka Type 2 or Historical Integrity). Unfortunately, in this first release you cannot use a change datetime value from the source table. It uses the system time when the update occurs. So, if you want to use your source system change datetimes, you won't be able to use Temporal Tables. Fortunately, the Merge statement is well suited.

Here is a sample Merge statement for a self managed Temporal Table.

Note, the Transaction and TABLOCKX is optional. I like using the table lock when there is a possibility of lock escalation. Two concurrent lock escalations are a guaranteed way of a deadlock. The TABLOCKX will ensure the Merge does not start until it can get a full table lock, so no chance of deadlock. This is at the cost of a little concurrency.

This statement has two insert statements. The first insert is for modified records (it's inserting the newer record) and the second insert is for new (not matched) records.

Please see this statement as a sample. You may want to change logic for EffectiveFromDate if you know the "real" time this record was active (CreatedDate, for example).

BEGIN TRANSACTION

     SELECT TOP 0 * from MySchema.MyTable WITH (TABLOCKX)

     INSERT INTO MySchema.MyTable(IsCurrent

                                                       ,MyKey1

                                                       ,MyKey2

                                                       ,EffectiveFromDate

                                                       ,EffectiveToDate

                                                       ,MyAttribute1

                                                       ,MyAttribute2

                                                       ,DatetimeChanged)

              SELECT 1 IsCurrent

                           ,MyKey1

                           ,MyKey2

                           ,DatetimeChanged EffectiveFromDate

                           ,'9999-12-31' EffectiveToDate

                           ,MyAttribute1

                           ,MyAttribute2

                           ,DatetimeChanged

            FROM

                (MERGE MySchema.MyTable Dst

                    USING LifeSTG.MySchema.MyTable Src

                            ON Dst.MyKey1=Src.MyKey1

                            AND Dst.MyKey2=Src.MyKey2

                            AND Dst.IsCurrent=1

                    WHEN NOT MATCHED

                            THEN--record from source doesn't exist in Destination

                                --INSERT the record into Destination

                                INSERT (IsCurrent

                                                                     ,MyKey1

                                                                     ,MyKey2

                                                                     ,EffectiveFromDate

                                                                     ,EffectiveToDate

                                                                     ,MyAttribute1

                                                                     ,MyAttribute2

                                                                     ,DatetimeChanged)

                                        VALUES (1

                                                                                  ,MyKey1

                                                                                  ,MyKey2

                                                                                  ,'1800-01-01'

                                                                                  ,'9999-12-31'

                                                                                  ,MyAttribute1

                                                                                  ,MyAttribute2

                                                                                  ,DatetimeChanged)

                    WHEN MATCHED --record from source exists in Destination,so check if any of the attributes have changed

                                --AND Dst.DatetimeChanged <> Src.DatetimeChanged --using ChangedDateTime checks on other columns not required

                                AND CHECKSUM(Dst.MyAttribute1

                                                                           ,Dst.MyAttribute2

                                                                           ,DatetimeChanged)

                                    <> CHECKSUM(Src.MyAttribute1

                                                                           ,Src.MyAttribute2

                                                                           ,DatetimeChanged)

                            THEN --Update the matching record as no longer current because the attributes have changed

                                UPDATE

                                        SET Dst.IsCurrent=0

                                            ,Dst.EffectiveToDate=Src.DatetimeChanged

                    OUTPUT   Src.MyKey1

                            ,Src.MyKey2

                            ,Src.MyAttribute1

                            ,Src.MyAttribute2

                            ,Src.DatetimeChanged

                            ,$Action as MergeAction

                ) MRG

    WHERE MRG.MergeAction='UPDATE'

COMMIT TRAN

Are Temp Tables Bad?

I have often said that temp tables are the hallmark of sloppy SQL programmers. Why can't these developers write correlated subqueries with outer joins without writing out intermediate tables? I even had a little wager for a bottle of whisky with Ron Soukup about the merits of temp tables just after I joined Microsoft in 1995. That was before I knew who Ron was. I was to show that a query could be faster without temp tables, but the old SQL Server 6.5 optimiser was didn't make it easy for me. Essentially, if you are using a temp table, you are saying that you can do it better than the database optimiser. Or in other words, the optimiser has room for improvement.

However, a colleague has brought to my attention a query that reliably runs faster with a temp table, and there isn't much I can do about it.

It would appear that SQL Server's optimiser uses the estimated rowcount when setting up/executing a sort. So that if the estimated rowcount is much higher than the actual, the sort becomes slightly more expensive than the same sort with an accurate estimation. This is quite intuitive to me, since a very large sort might use a different algorithm than a very small sort. (In the old mainframe days we always had to tell the sort program how many records it was going to sort.)

In my query without a temp table, SQL Server estimates 26,000 rows will be sorted, while the actual number of rows to be sorted is 36. Of course the sort after the temp table quite accurately estimates 36 rows. So, by inserting the 36 rows into a temp table before sorting, the optimiser will know quite accurately how many records it will sort. To further support this hypothesis, I included a "top 36" in the query without a temp table, and low and behold, it estimated 36 rows in the sort and ran faster (in the same time as the temp table query).

I feel that this performance effect is more of a curiosity than a valid justification to use temp tables in queries with Order By clauses. Also, it is very likely that in a future version of SQL Server the Optimiser team will enhance the product to dynamically determine what sort will be employed during query execution. Then the temp table query will be slower.

The original query was from a customer database, but here is an equivalent query that I have written on my home Perfmon database. If you want to reproduce the effect, the key is to have a query that estimates a very large number of rows in the sort, while the actual number is very small.

The picture shows the execution plan for the two queries. The first query (no temp table) took 53% of the total resources, while the second and third queries (with temp table) only used 47% of the total resources. This was further evidenced from the temp table queries using 10% less cpu than the original query. 328 ms of cpu versus 297 ms for the temp table queries.

DECLARE @date DATETIME
DECLARE @value real
select @date = '2010-01-15 15:50:00', @value = 700
--select * from (
select --top 53
MAX(CounterDateTime) CounterDateTime,
SUM(CounterID) CounterID,
CounterValue CounterValue
FROM CounterDatafacts with (nolock)
WHERE CounterValue between @value and @value+2
and CounterDateTime between @date and DATEADD(hh,1,@date)
Group By CounterValue --) as x
ORDER BY CounterDateTime
--Same query query using temp table
select MAX(CounterDateTime) CounterDateTime,
SUM(CounterID) CounterID,
CounterValue CounterValue
INTO #TEMP
FROM CounterDatafacts with (nolock)
WHERE CounterValue between @value and @value+2
and CounterDateTime between @date and DATEADD(hh,1,@date)
Group By CounterValue
SELECT * FROM #TEMP ORDER BY CounterDateTime
DROP TABLE #Temp

Circular Relationships

I am occasionally reviewing database designs and one of the things I like to check is normalisation. One indication that a table structure isn't in third normal form is the existence of circular relationships. That is where you can navigate from one entity back to itself via circular relationships. I should stress that the existance of a circular relationship does not necessarily mean that we aren't in 3rd normal form, but it is a good clue.

Here is an example in the picture of a circular relationship. From the Products table you can get to ProductType directly or via ProductGroup.

To search for circular relationships that are declared by referential constraints, you can use the following SQL query. (Note, it is only looking for a three level relationship, and it could be enhanced to find deeper redundant relationships.)

select
OBJECT_NAME(fk.parent_object_id) Child
,OBJECT_NAME(fk.referenced_object_id) Parent
,OBJECT_NAME(fk1.referenced_object_id) Intermedidate
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.parent_object_id and c.column_id=fkc.parent_column_id
where fkc.constraint_object_id=fk.object_id) FK_FirstColumn_FirstRelationship
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.referenced_object_id and c.column_id=fkc.referenced_column_id
where fkc.constraint_object_id=fk.object_id) PK_FirstColumn_FirstRelationship
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.parent_object_id and c.column_id=fkc.parent_column_id
where fkc.constraint_object_id=fk1.object_id) FK_FirstColumn_IntermediateRelationship1
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.referenced_object_id and c.column_id=fkc.referenced_column_id
where fkc.constraint_object_id=fk1.object_id) PK_FirstColumn_IntermediateRelationship1
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.parent_object_id and c.column_id=fkc.parent_column_id
where fkc.constraint_object_id=fk2.object_id) FK_FirstColumn_IntermediateRelationship2
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.referenced_object_id and c.column_id=fkc.referenced_column_id
where fkc.constraint_object_id=fk2.object_id) PK_FirstColumn_IntermediateRelationship2
from sys.foreign_keys fk
inner join sys.foreign_keys fk1 on fk1.parent_object_id=fk.parent_object_id
inner join sys.foreign_keys fk2 on fk2.parent_object_id=fk1.referenced_object_id
and fk2.referenced_object_id=fk.referenced_object_id

Remember, there is a lot of information in the relational catalog. We should use when we want need information about our data structures.

When to use clustered indexes

I often found experienced database designers, new to SQL Server, will come with preconceived ideas about the optimiser and storage engine. One of these ideas is that clustered indexes should be used sparingly, since they come at a cost with limited benefit. With SQL Server, this is by and large untrue, and as a general rule, most tables will be better off with one of the indexes being clustered.

SQL Server (and Sybase) have historically had a structurally different way of maintaining clustered indexes. They actually use the index leaf pages to hold the data columns, which enables them to save space, reduce the number of pages touched in an index path, and use them to very efficiently sequence scan.

There is a useful performance analysis of clustered versus non-clustered PK index on http://technet.microsoft.com/en-us/library/cc917672.aspx This paper looks primarily at the cost and throughput of inserts, updates and deletes. By and large, the clustered indexes performed better. There is one significant exception, where there are high volume concurrent inserts, with keys in close proximity. In this situation, concurrent can compete for locks and latches with other active transactions with similar keys. By the way, this does not necessarily mean that you shouldn't use a clustered index for the table, rather, it might mean your clustered index should be on a key that won't create hot spots. For example tbTransaction might have contention with TransactionID clustered, but a clustered index on AccountID would be less contentious and might even be better serving queries.

One area that this paper doesn't look at, and in my view, is one of the most important reasons for clustering, is queries that join very large tables. If you are joining two or more very large tables, the optimiser will like to have the opportunity to Merge Join the larger tables. For example, a query that was to join tbTransaction with tbTransactionCharges and many other smaller tables is likely to benefit from having both large tables clustered in the same sequence. This could be TransactionId, or, perhaps more likely, AccountID. If queries on these two tables were often filtered by particular accounts (or tbAccount columns) then having all 3 tables clustered by AccountID (and also TransactionID or TransactionDate for the transaction tables) is likely to provide very good join performance.

We are very fortunate with relational databases, in that we can change the index structure without having to rewrite any application queries. However, I would encourage database designers to analyse in some detail the activity on the biggest tables, and design appropriate clustered indexes early on. Clustereed indexes are much more difficult to change at a later date than non clustered indexes. Also, for those smaller tables, make one of the indexes (typically the natural primary key) clustered.

For a real-time demonstration of a database application continuously processing about 300 records/sec see http://RichardLees.com.au/Sites/Demonstrations The Perfmon application is close to real-time and is processing about 300 perfmon records all day, every day, on commodity 32 bit (yes I really should upgrade) hardware.

How to Avoid SQL Reserved Words

Have you ever been querying a database and received a syntax error because one of your column, table or some other object name is clashing with an SQL reserved word? If so, here is an easy way to avoid reserved words during your development cycle. In simple terms it joins a list of reserved words with your catalog columns.

1. Simply download the ReservedWords.csv from http://richardlees.com.au:8080/dropzone/SQLReservedWords.csv Or if you prefer, copy the words from SQL Server books online and load them into a table.
2. Load the words into this table
   create table ReservedWords (ReservedWord varchar(128), ReserveWordList varchar(128))
3. Run the following query

select o.name [Object Name],c.name [Column Name], w.ReserveWordList
from sys.columns c
inner join coreddsdevtemp..ReservedWords w on w.ReservedWord=c.[name]
inner join sys.objects o on c.object_id=o.object_id and o.type NOT IN ('S','IT')
order by 1,2

You will get a list of all the objects that have column names on the SQL 2008, ODBC, and SQL future reserved lists. By the way, you might want to filter out the ODBC words if they are not appropriate for you.

I should add that it isn't fatal to have reserved words in your columns. It just means that you might have to use delimiters around your column names.