SQL Server Support

PostgresDoc now supports SQL Server. SQL Server does not have the required JSON support, so the SQL Server version serializes to xml instead. The serializer supports .net classes, records and discriminated unions.

Create your store like this:

let storeSql = SqlStore "your connection string"

and create tables like this:

CREATE TABLE [dbo].[card](
	[Id] [uniqueidentifier] NOT NULL,
	[Data] [xml] NOT NULL,
 CONSTRAINT [PK_card] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)

Inserting, updating and deleting work the same as for Postresql. Querying requires the use of Sql Server's XML querying capabilities.

Querying

[
        "sourceUrl", box url
        "userId", box userId
]
|> query<Deck> store "select [Data] from [deck] 
where Data.value('(/Deck/userId)[1]', 'uniqueidentifier') = @userId 
and Data.value('(/Deck/sourceUrl)[1]', 'nvarchar(512)') = @sourceUrl" 

There are many more options available for querying sql server xml.

Indexing

Firstly, enable XML indexing:

EXECUTE AdventureWorks.sys.sp_db_selective_xml_index AdventureWorks, TRUE

NOTE: This step appears to fail for Azure Sql databases.

Primary XML Indexing

Sql server xml indexes traditionally index everything, which is convenient, bulky and slow.

create primary xml index xi_card on [card] (Data)

Selective XML Indexing

Since 2012 Sql Server also has selective indexing.

CREATE SELECTIVE XML INDEX SXI_Card
ON [card](Data)
FOR 
(
    pathDeckId = '/Card/deckId'
)
GO

select * from [card]
where 
Data.exist('/Card/deckId[.="13694d26-0d5f-4cbe-9ad1-9dc9b096bd3f"]') = 1