SQLRAG

SQLRAG

Make SQL Server also perform vector similarity query

Functions have been implemented:

• Various similarity comparison CLR functions: CosineSimilarity, EuclideanDistance... [Reference (https://chat.openai.com/share/40723def-afdd-4f83-adb3-fbcae34b617a)]
• Call Openai API (GetEmbedding, ChatCompletion...) through CLR function

Install

Minimum installation requirements: SQL Server 2022, Visual Studio 2022 (SSDT needs to be installed)

1. First, enable SQL Server CLR:

	use SQLRAG
	sp_configure ' show advanced options ' , 1 ;  
	RECONFIGURE;  
	sp_configure ' clr enabled ' , 1 ;  
	RECONFIGURE;  

2. There are several ways to install:

   • Installation can be completed by using SQLRAG_CREATE.sql provided in release directly to execute (suitable for first-time installers)
   • Use the dacpac file provided in release, and use the content of [web page (https://learn.microsoft.com/zh-tw/sql/relational-databases/data-tier-applications/upgrade-a-data-tier-application?view=sql-server-ver16)] to install it.
   • Use the original code provided in release, use Visual Studio 2022 to modify the database of each project and directly deploy the plan after connecting to it.

3. If you want to use OpenaiFunction, the previous approach was to enter your actual OPENAI_API_KEY in OpenaiFunction.cs. This approach cannot ensure the security of API KEY. In this version, I added the dbo.EncryptedKeys data table to the SqlRAG database, where the KeyValue part is encrypted through credentials. It looks complicated, but I have already processed the automatic call part. The developer only needs to encrypt the API KEY and write it into the data table through the following SQL syntax.

	use SQLRAG
	Declare @cleartext varchar ( 512 ) = ' sk-輸入你的OPENAI_API_KEYAPI KEY '
	Declare @encrytext varbinary( 4000 ) = EncryptByCert(Cert_ID( ' SqlRAGCertificate ' ), @cleartext) 

	INSERT INTO [SQLRAG].[dbo].[EncryptedKeys] 
    	VALUES ( N ' OPENAI_API_KEY ' , N '調用OPENAI所用之API KEY ' ,@encrytext );  

Then, you need to decrypt it by explicitly declaring which credential and the corresponding password:

    DecryptByCert(Cert_ID( ' SqlRAGCertificate ' ), EncryptByCert(Cert_ID( ' SqlRAGCertificate ' ), @cleartext) , ' P@ssw0rd ' )

If you are an Azure Openai Service user, you need to encrypt the API key and endpoint and save it in

	use SQLRAG
	Declare @cleartext varchar ( 512 ) = ' *** '
	Declare @encrytext varbinary( 4000 ) = EncryptByCert(Cert_ID( ' SqlRAGCertificate ' ), @cleartext) 

	Declare @cleartext2 varchar ( 512 ) = ' https://***.openai.azure.com '
	Declare @encrytext2 varbinary( 4000 ) = EncryptByCert(Cert_ID( ' SqlRAGCertificate ' ), @cleartext2) 

	Declare @cleartext3 varchar ( 512 ) = ' *** '
	Declare @encrytext3 varbinary( 4000 ) = EncryptByCert(Cert_ID( ' SqlRAGCertificate ' ), @cleartext3) 

	INSERT INTO [SQLRAG].[dbo].[EncryptedKeys] 
    	VALUES ( N ' AZURE_OPENAI_API_KEY ' , N '調用Azure Openai Service 所用之API KEY ' ,@encrytext );  
	INSERT INTO [SQLRAG].[dbo].[EncryptedKeys] 
    	VALUES ( N ' AZURE_OPENAI_ENDPOINT ' , N '調用Azure Openai Service 所用之endpoint ' ,@encrytext2 );  
	INSERT INTO [SQLRAG].[dbo].[EncryptedKeys] 
    	VALUES ( N ' OPENAI_API_VERSION ' , N '調用Azure Openai Service 所用之API Version ' ,@encrytext3 );  

4. QueryIntentCache_demo.sql (semantic cache example) and ChatCompletion_demo.sql (ChatGPT answer example)