Temp Tables

[davidtme]

I have an idea to get temp tables working but I’ll like to just check this would be a valid addition and not too hacky.

I would like to add an extra parameter on the type provider call something like TempTableDefinition

use cmd = new SqlCommandProvider<"
	SELECT *
	FROM Account a
		INNER JOIN #Temp t ON a.Id = t.Id
	WHERE a.Id = @Id
	", ConnectionString, TempTableDefinition = "
		CREATE TABLE #Temp
		(
			Id INT
		)">(connectionString)

The command and temp table create text would need to be rewritten in the design phase to a Global Temporary table (ie ##Temp) then before executing sp_describe_undeclared_parameters the create temp table query is run, afterwards the temp table can be dropped.

sp_describe_undeclared_parameters is happy to use a global temporary table

-- Setup database
DROP TABLE IF EXISTS Account
CREATE TABLE Account
(
	Id INT
);
-- End setup

DROP TABLE IF EXISTS ##Temp
CREATE TABLE ##Temp
(
	Id INT
);

EXECUTE sp_describe_undeclared_parameters N'
SELECT *
FROM Account a
	INNER JOIN ##Temp t ON a.Id = t.Id
WHERE a.Id = @Id
'
DROP TABLE IF EXISTS ##Temp

-- Reset database
DROP TABLE IF EXISTS Account

If that all works I would also like to try and create the parameters by for the Execute by sending the temp table to the database and reading the row metadata back

DROP TABLE IF EXISTS #Temp
CREATE TABLE #Temp
(
	Id INT
);
SELECT *
FROM #Temp
DROP TABLE #Temp

[dmitry-a-morozov]

@davidtme Overall temp tables are better off to be avoided in T-SQL coding.
At same time I understand sometimes it's required for performance optimization.
I would suggest following workarounds:

1. Re-write query to avoid temp tables
2. Extract code into stored procedure or function
3. Use raw ADO.NET

I think library should discourage bad patterns in same way F# for example discourages mutability.
Anyway, this is only my opinion.
Currently I don't work with Sql Server and don't have capacity to review and merge your PR.
Somebody else should do it.
@dsevastianov ?

[davidtme]

@dmitry-a-morozov I’m creating a database sync project and a part of that is loading data (few million rows) from one database (B) to another database (A) which will then be using in a purge, change check and extract .

Database A has a fixed schema so I can’t add a table variable, stored procedures or function.

I'm using table variable / stored procedures in Database B which I can control.

At the moment I’m using raw ADO.NET but I don’t get the lovely SQL checking with FSharp.Data.SqlClient

My changes are an opt-in which I hope will discourage normal users but it's there in anyone needs it.

[smoothdeveloper]

@davidtme I've been in similar spots, needing to use temp tables.

The reasonable thing that woud help I felt, would be to allow typed datatables to be constructed without running a query, and then using that table as a container to put resuts of a plain ADO.NET query, which matches the table but uses temp tables.

The drawback is if you have a mismatch with your query and the typed table, it would not be caught at compile time, my work around for this are either:

• unit tests
• comments in the statements pointing to the other statement that needs to match the schema

This would not require adding additional parameter to the type provider, which becomes unwieldy to maintain.

[davidtme]

Hi @smoothdeveloper thanks for the comment but I’m not sure I quite follow the non-running query / container table - Is this something that can be done now?

[smoothdeveloper]

@davidtme I've touched on the issue in #228 regarding exposing constructor to datatable, the potential implementation in the library involves defining the columns while current mechanisms rely on ADO.NET to set the table schema at runtime.

The usage of such solution would be:

type MyCommandJustForTableTemplate< """
        ... sql statement with schema which matches the 
        actual statement you need to 
        use temp tables with ...
    """
    , ResultType = DataTable 
    >

let table = MyCommandJustForTableTemplate.Table()

let plainAdoCommandWithTempTable = makeMyCommand()
table.Load(plainAdoCommandWithTempTable.ExecuteReader())

[dmitry-a-morozov]

@davidtme Can you create normal table instead of temp in Database B and drop it later?

[davidtme]

@dmitry-a-morozov It’s a nice idea but the normal table would have to live in database B at design time so I could use it (moving into the realms of dev/prod behaving differently). Also my extract part runs a few processes in parallel using different connections with different temp tables. One other thing if anything goes wrong the real table wouldn’t be deleted but temp table are thrown away when the connection closes.

[smoothdeveloper]

related: #278 it looks interesting solution to the problem.

[smoothdeveloper]

Fixed in #302, thanks a lot for your patience and excellent contribution @davidtme.