call us toll-free +1 855 855 3600
 
  • Home
  • >
  • Blog
  • >
  • How to Implement a SQL Server View Based on Temporary Table
DB Best Chronicles
 
Talks on Big Data, Mobile Apps, Web and Software Development

How to Implement a SQL Server View Based on Temporary Table

Posted by | On August 13th, 2012 | In Big Data, Database Migration
 

If you ask any experienced SQL Server developer: “Can I build a view on temporary table?” the answer will be “Of course not. And why on Earth you need it?” Well, Oracle developers can tell you why. Temporary tables are very useful when you have to separate data between users/sessions and creating view on them is just convenient way to store some logic. SQL Server developers used to deal without it but when they are trying to move database from Oracle they will have to find workaround because it’s pretty common pattern in Oracle.

Let’s take a look at how it works.

SQL Server View

First thing that comes to mind is to use regular table instead of temporary. To emulate data separation between sessions we can add “spid” column with default value @@spid (system function that returns the session ID of the current user process). Than we need to create view named exactly like original temporary table (of course we have to name our regular table differently) and add where condition “spid = @@spid”. So select from the view will return only that part of data, which was populated in current connection. Next challenge here is that in Oracle data from temporary table is automatically deleted on rollback/commit.

If we are working with regular SQL Server table we have to do it manually. In the best case it will be one entry point in original application code where we can simply add something like “delete temp_mock where spid = @@spid”, otherwise it could be difficult to find all references to the table and add delete statements there. But let’s assume that our original application architecture was good enough and purging table is not a challenge. What could be a challenge is concurrent access to data. Multiple queries on a same table could – and very likely will – cause problems with locks and deadlocks, which of course are solvable, but this is a topic for separate discussion. So main three steps of the algorithm are:

  1. Create table and view (very simple)
  2. Add “delete” statement to all required routines (could be a challenge)
  3. Fix locks/deadlocks issues (definitely would be a challenge)

In many cases this algorithm will be good enough but what if we have to migrate several hundreds of temporary tables and we want to make the process automated? CLR could be a good solution.

Let’s assume that we have to migrate simple temporary table #Orders with just three columns “ID”, “CustomerID” and “Date”, and a view build on that table:

1
2
3
4
5
6
7
8
create view v_OrdersByDate
as
select
c.name,
o.date
from #Orders o
join Customer c
on c.ID = o.CustomerID

If you try to create such view in SQL Server query will failed with error:

Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.

All you need is just to wrap temporary table with CLR table-valued function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
public class Record
{
public SqlInt32 Id;
public SqlInt32 CustomerId;
public SqlDateTime Date;

public Record(SqlInt32 id, SqlInt32 customerId, SqlDateTime date)
{
Id = id;
CustomerId = customerId;
Date = date;
}
}
public static void FillRow(object obj, out SqlInt32 id, out SqlInt32 customerId, out SqlDateTime date)
{
var record = (Record)obj;
id = record.Id;
customerId = record.CustomerId;
date = record.Date;
}
[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read, FillRowMethodName= "FillRow", TableDefinition = "id int, CustomerID int, [Date] datetime")]
public static IEnumerable GetTempOrdersCLR()
{
var result = new ArrayList();

using (var connect = new SqlConnection("context connection=true"))
{
connect.Open();
using (var cmd = new SqlCommand("select id, customerID, [Date] from #Orders", connect))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
result.Add(new Record(reader.GetSqlInt32(0), reader.GetSqlInt32(1), reader.GetSqlDateTime(2)));
}
}
}
return result;
}

And after that you can create view on this CLR function instead of temporary table:

1
2
3
4
5
6
7
8
create view v_OrdersByDate
as
select
c.name,
o.date
from [dbo].[GetTempOrdersCLR]() o
join Customer c
on c.ID = o.CustomerID

It will work.

Not it may seem that CLR is a silver bullet for dealing with almost all SQL Server limitations. Of course it is not.

CLR require some communication overhead. Performance tests show that CLR is way slower than direct select from temporary table:]

Records in #Orders GetTempOrdersCLR
(sec)
#Orders
(sec) 
1,000 0.020 0.003
10,000 0.050 0.003
100,000 0.500 0.020
1,000,000 5.000 0.100

These numbers does not mean that we can’t use CLR wrappers for temporary tables. First of all, usually these tables don’t contain millions of records. Second, we can try to optimize it. For example, we can add key parameter and use cross apply instead of join, if the amount of data in the left and right tables is very different. Here are results for 1 000 000 records in “#Orders” and 50 000 records in “Customer”.

A join #tab 0.100 sec
B join dbo. GetTempOrdersCLR () 5.000 sec
C cross apply dbo. GetTempOrdersCLR (c.CustomerId) 1.000 sec

 

So CLR wrapper is safe, fast to implement and could be very simply automated. Regular table algorithm is faster, but sometimes very complicated.

 

Comments

comments

Comments are closed.