Recently I’ve been asked if it’s possible to partially commit the transaction. Interestingly enough – the answer is not simple. SQL Server does not support nested transactions. Although there are a few tricks you can use.

First is savepoints. This will allow you to save transaction state at some point, rollback to that point and commit everything you did prior that point. Obviously it will help you to commit only first (beginning) part of the transaction.

Let’s see it in action. Let’s create the small table.

Next, let see savepoints in action.

Good. Now assumming we would like to do opposite task – commit the “tail’ actions after specific point. Unfortunately you cannot do it with regular T-SQL statements. Fortunately there is the trick – temporary table variables. They are not supporting transaction context. Let’s see it in action:

As you can see, you can create temporary table variable, populate it with data, rollback the transaction and next save data in the separate transaction. It worth to mention that regular temporary tables do not work that way – they fully support transaction context.

One of the practical usage of this scenario is audit on rollback. You can save required data into temp table variable, rollback data modification attempt and write audit information to the audit table.

P.S. Just wonder if anybody reads it 🙂

Please check this SP. Does it look familiar?

Easy to implement. Easy to support. Only minor problem – it never uses indexes. Never. Period.

Let’s check that with simplified example

For us, who came from imperative development world, it does not look right. How come that SQL Server cannot do the simple optimization and determine, that parameter is not null. Well, it does not. It does not know this during the compilation stage so it would scan entire table and apply both predicates to every row. Even if first predicate needs to be checked only once.

So how to deal with that? The most optimal way is to specify the list of all parameter combination. Something like that.

Every select would be optimized. Brute force approach will require 2^N selects where N is the number of parameters, which is introducing some limitations with N > 1..3. In real life it could be slightly better because if you can force index seek with one predicate, you should not really worry about others. So it could be done in the next way:

Still requires quite a few different selects and hard to maintain.

Another solution is dynamic SQL:

It would lead to recompilations of dynamic SQL for every SP call. Well, this is the downside of that method. Fortunately there is the good chance that plan would be in the cache especially if SP is called often enough so compilation would not be expensive.

Last Sunday I wrote about cross joins. Today I want to show another example where cross joins could be useful.

Assuming you have 2 tables in the database. One is Units. Another 1 is UnitType1Settings which contains set of the settings for units with UnitType=1. See below:

As the disclaimer, this design is far from optimal. Please don’t use it in your system. Even if I’m using it only as the example, I saw similar approaches in real production systems.

In any case, there were about 20 settings per unit, so developers implemented the trigger like below:

Obviously this is not the best implementation. Cursor, 20 separate inserts. Not even talking about the triggers in general.

Let’s test it with the insert statement which inserts 20 units and 10 of them are UnitType=1

Original trigger produces 30 (and in real life 200) separate inserts with plan like that:

So how can you optimize that (assuming you want to keep trigger in place). So first step is get rid of the cursor and do something like:

If you look at the plan, you can see:

Better, but still requires multiple (20 scans) of the inserted table. And this is where cross join can help:

This implementation basically builds rowset with 3 (20) settings and next simply do the Cartesian product with all units with UnitType = 1

As you can see in this plan it uses only 1 scan which is much more efficient than previous implementations

As we know, cross joins without WHERE clause produce the Cartesian product of the tables. So, for example, if first table has 5 rows and second table has 3 rows, result set will have 15 rows with all possible combination of the values from the both tables.

Quite limited real-life usage though – I believe I used it maybe 2 times during last 5 years. First time it was the task to combine account specific configuration settings and system configuration settings into one row. Something like:


select
a.ConfigValue1
,a.ConfigValue2
,c.GlobalConfigValue1
,c.GlobalConfigValue2
from
dbo.Accounts a cross join dbo.Config c

Second time it was similar task – combined information from master record and list of detail record IDS in XML format. Could be easily accomplished with the function of course.


;with Details(UserList)
as
(
select UserId
from dbo.Users
where AccountId = @AccountID
for XML raw('User'), Root('Users')
)
select a.AccountId, a.AccountNumber, d.UserList
from dbo.Accounts a cross join Details d
where a.AccountId = @AccountID

Only problem with this approach – If one of the rowsets in the join is empty, it behaves as inner join and would not return any rows. This is absolutely correct behavior if you think about it, although you typically don’t expect it to work like that when you write it. You can see it with the following example:


;with CTE1(field1)
as
(
select 'field1' where 1 = 1
)
,CTE2(field2)
as
(
select 'field2' where 1 = 0
)
select CTE1.Field1, CTE2.Field2
from CTE1 cross join CTE2

So if you want to handle it properly, you need to change it from cross-join to full outer join with always true predicate. ;with CTE1(field1)
as
(
select 'field1' where 1 = 1
)
,CTE2(field2)
as
(
select 'field2' where 1 = 0
)
select CTE1.Field1, CTE2.Field2
from CTE1 full outer join CTE2 on
1 = 1

It will do the trick.