Temporal Tables FAQ

I had the pleasure of presenting Temporal Tables to the Capital Area .NET User Group on December 10, 2024.  Some interesting FAQ arose from that meeting so I thought it would be good to share it on my blog for reference.

 

1. Is the historical table logged to the Transaction log in the same way a conventional table is?  Will we “see” the inserts in the Tran log the same way we see them for a normal table?

No

 

2. Are Temporal Tables available in Azure?

Yes, in Azure SQL Database and Azure SQL Managed Instance

 

3. Will Temporal Tables work with graph tables?

No, Node and edge tables can't be created as system-versioned temporal tables

Ref: https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-architecture?view=sql-server-ver16

 

4. What triggers the purge of SQL Server Temporal Tables?

A background task is created to perform aged data cleanup for all temporal tables with finite retention period.

ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/temporal-tables-retention-policy?view=azuresql

 

5. Can I modify recs in the history table with versioning OFF? 

Yes

 

6. Can I alter the current table with versioning ON? 

Yes, alters both tables simultaneously

 

7. Does EF Code First Support Temporal Tables (TT)?

Yes, EF Core 6.0 and later supports:

• The creation of temporal tables using Migrations
• Transformation of existing tables into temporal tables, again using Migrations
• Querying historical data
• Restoring data from some point in the past

 

8. When are Temporal Tables NOT a good fit? 

For Static data where a user can't change a field.

The following industries rarely, if ever, are users allowed to delete any data

• Financial services (i.e. stock purchases, banking)
• Purchases
• GPS Location Tracking
• Fraud Detection(Location of purchases, IP/location of login)
• Tracking appointments (Resolve Customer service issues)
• Car Rental company, tracking owner and mileage when vehicle was in possession
• Hotel room rental
• Airline seat charts, who sat where in which flight

 

9. Are there other approaches are available for managing historical data in the temporal history table?

Yes, The following four methods are available:

1. Stretch Database
2. Table Partitioning
3. Custom Cleanup Script
4. Retention Policy

 

10. Can Temporal tables be used with partitioned databases?

Yes, with some limitations. If current table is partitioned, the history table is created on default file group because partitioning configuration is not replicated automatically from the current table to the history table.

 

11. Can the temporal table be placed in another DB?

No, History table must be created in the same database as the current table.

However, it can be placed in a different schema within the same database.

Also, Temporal querying over Linked Server is not supported.

 

12. Can it be used with Elastic DBs?

Yes

 

13. Can I alter table schema with sys versioning = on? 

No

 

14. Can I add additional fields to History table?

No, fields and field nullability must be identical

 

15. Is there automatic truncation of history table?

Yes, using HISTORY_RETENTION_PERIOD = 6 MONTHS

 

Questions on Copilot Data Privacy

Q: concerned about what Microsoft and / or the US government can do with the data for a custom copilot. I’ve looked at the Microsoft copilot documentation but I didn’t find anything that clearly states what Microsoft can and cannot do with data used in custom copilots, do you have any resources that you can share?

 

A: Microsoft posted info about this topic specifically at https://learn.microsoft.com/en-us/legal/cognitive-services/openai/data-privacy?context=%2Fazure%2Fcognitive-services%2Fopenai%2Fcontext%2Fcontext#see-also

 

In a nutshell:

Your prompts (inputs) and completions (outputs), your embeddings, and your training data:

• are NOT available to other customers.
• are NOT available to OpenAI.
• are NOT used to improve OpenAI models.
• are NOT used to improve any Microsoft or 3rd party products or services.
• are NOT used for automatically improving Azure OpenAI models for your use in your resource (The models are stateless, unless you explicitly fine-tune models with your training data).
• Your fine-tuned Azure OpenAI models are available exclusively for your use.

The Azure OpenAI Service is fully controlled by Microsoft; Microsoft hosts the OpenAI models in Microsoft’s Azure environment and the Service does NOT interact with any services operated by OpenAI (e.g. ChatGPT, or the OpenAI API).

 

 

 

Q: Does Microsoft have the same Data Privacy policy for Copilot studio as Azure AI Studio?  Is there similar documentation for custom copilots created in copilot studio?

 

A: It’s seeming like it’s the same.  After logging into Copilot Studio, browse to https://www.microsoft.com/licensing/terms/product/PrivacyandSecurityTerms/all .  From there, you can download the Data Protection Addendum from https://aka.ms/DPA. (see attached).  On P.5 it states:

 

Nature of Data Processing; Ownership

Microsoft will use and otherwise process Customer Data, Professional Services Data, and Personal Data only as described and subject to the limitations provided below (a) to provide Customer the Products and Services in accordance with Customer’s documented instructions and (b) for business operations incident to providing the Products and Services to Customer. As between the parties, Customer retains all right, title and interest in and to Customer Data and Professional Services Data. Microsoft acquires no rights in Customer Data or Professional Services Data, other than the rights Customer grants to Microsoft in this section. This paragraph does not affect Microsoft’s rights in software or services Microsoft licenses to Customer.

 

 

'Build Your Own Copilot" Resource Links

Interested in building your own Copilot using Azure AI Studio?  Listed below are some useful links:

• Azure AI Studio Architecture
• Quickstart: Create a project and use the chat playground in Azure AI Studio
• Tutorial: Deploy an Enterprise Chat web app
• AI Studio FAQ

 

May '24 Regional Tech Events

User Groups

• May 2: Twin Cities .NET User Group
• May 7: Ohio North Database Training
• May 8: Azure Cleveland
• May 16: GLUG.NET
• May 22: Cleveland C# User Group

 

Conferences

• May 3: Stir Trek
• Jun 27-28: Kansas City Developer Conference
• Jul 26: Cincy Deliver

 

Apr '24 Regional Tech Events

User Groups

• Apr 2: Ohio North Database Training
• Apr 4: Twin Cities .NET User Group
• Apr 10: Azure Cleveland
• Apr 18: GLUG.NET
• Apr 24: Cleveland C# User Group

 

Conferences

• May 3: Stir Trek

 

Trainers in ML.NET

Machine learning tasks like regression and classification contain various algorithm implementations. 

Some tasks may utilize the same algorithm, such as the SDCA algorithm in both Binary Classification and Regression tasks

 

In some cases, the problem you are trying to solve and the way your data is structured does not fit well into the current algorithm.

If so, consider using a different algorithm for your task to see if it learns better from your data.

 

A trainer identifies a single algorithm used for a single task (i.e. Trainer = Algorithm + Task).

Listed below is a summary of trainers available in ML.NET. For more info, see guidance on which algorithm to choose.

 

Trainer	Algorithm	Task	ONNX Exportable
SdcaLogisticRegressionBinaryTrainer	SDCA	Binary classification	Yes
SdcaNonCalibratedBinaryTrainer	SDCA	Binary classification	Yes
SdcaMaximumEntropyMulticlassTrainer	SDCA	Multiclass classification	Yes
SdcaNonCalibratedMulticlassTrainer	SDCA	Multiclass classification	Yes
SdcaRegressionTrainer	SDCA	Regression	Yes
AveragedPerceptronTrainer	Averaged Perceptron	Binary classification	Yes
LbfgsLogisticRegressionBinaryTrainer	L-BFGS	Binary classification	Yes
LbfgsMaximumEntropyMulticlassTrainer	L-BFGS	Multiclass classification	Yes
LbfgsPoissonRegressionTrainer	L-BFGS	Regression	Yes
SymbolicSgdLogisticRegressionBinaryTrainer	Symbolic stochastic gradient descent	Binary classification	Yes
OnlineGradientDescentTrainer	Online gradient descent	Regression	Yes
LightGbmBinaryTrainer	Light gradient boosted machine	Binary classification	Yes
LightGbmMulticlassTrainer	Light gradient boosted machine	Multiclass classification	Yes
LightGbmRegressionTrainer	Light gradient boosted machine	Regression	Yes
LightGbmRankingTrainer	Light gradient boosted machine	Ranking	No
FastTreeBinaryTrainer	Fast Tree	Binary classification	Yes
FastTreeRegressionTrainer	Fast Tree	Regression	Yes
FastTreeTweedieTrainer	Fast Tree	Regression	Yes
FastTreeRankingTrainer	Fast Tree	Ranking	No
FastForestBinaryTrainer	Fast Forest	Binary classification	Yes
FastForestRegressionTrainer	Fast Forest	Regression	Yes
GamBinaryTrainer	Generalized additive model	Binary classification	No
GamRegressionTrainer	Generalized Additive Model	Regression	No
MatrixFactorizationTrainer	Matrix Factorization	Recommendation	No
FieldAwareFactorizationMachineTrainer	Field Aware Factorization Machine	Binary classification	No
OneVersusAllTrainer	One Versus All	Multiclass classification	Yes
PairwiseCouplingTrainer	Pairwise Coupling	Multiclass classification	No
KMeansTrainer	KMeans	Clustering	Yes
RandomizedPcaTrainer	Randomized Pca	Anomaly detection	No
NaiveBayesMulticlassTrainer	Naive Bayes Multiclass	Multiclass classification	Yes
PriorTrainer	Prior	Binary classification	Yes
LinearSvmTrainer	Linear Svm	Binary classification	Yes
LdSvmTrainer	Ld Svm	Binary classification	Yes
OlsTrainer	Ols	Regression	Yes

 

 

What is Auto-GPT?

Auto-GPT is an experimental project developed by Significant Gravitas.  It’s an open-source Python application powered by GPT-4.

 

Unlike ChatGPT, Auto-GPT does not rely on human prompts to operate. It can self-prompt and tackle subsets of a problem without human intervention.  It works by pairing GPT with AI agents that can make decisions and take actions based on a set of rules and predefined goals.

Auto-GPT is important and relevant because it showcases the potential of language models like GPT-4 to autonomously complete different types of tasks. It has the ability to write and execute its own code using GPT-4, allowing it to debug, develop, and self-improve recursively. One of the advantages of Auto-GPT is its ability to continuously self-improve. It can debug, develop, and enhance its own capabilities recursively.

 

Accessing Auto-GPT requires specific installed software and familiarity with Python, and an API key from OpenAI.  It runs locally on a Mac, PC, or Docker image.

 

For a complete tutorial on how to use AutoGPT, visit https://youtu.be/v-5AWQlTFw8

 

For more info, see What is Auto-GPT and What Is the Difference Between ChatGPT vs Auto-GPT?

 

ML.NET Task Metrics

ML.Net has the capability of utilizing 7 different Machine Learning Tasks via the MLContext object:

1. Binary Classification
2. Multi-class/text Classification
3. Regression and Recommendation
4. Clustering
5. Ranking
6. Anomaly Detection
7. sentence similarity

 

Each task offers various performance metrics for evaluating the model after training is completed

These metrics are properties accessible via the Evaluate() method within each task object (i.e. MLContext.MLTask.Evaluate()

 

Sample Code Snippet

    static void Main(string[] args)

    {

        MLContext mlContext = new MLContext();

 

        // 1a. Create training data

        HouseData[] houseData = {

               new HouseData() { Size = 1.1F, Price = 1.2F },

               new HouseData() { Size = 1.9F, Price = 2.3F },

               new HouseData() { Size = 2.8F, Price = 3.0F },

               new HouseData() { Size = 3.4F, Price = 3.7F } };

 

        // 1b. Import training data

        IDataView trainingData = mlContext.Data.LoadFromEnumerable(houseData);

 

        // 2. Specify data preparation and model training pipeline

        var pipeline = mlContext.Transforms.Concatenate("Features", new[] { "Size" })

            .Append(mlContext.Regression.Trainers.Sdca(labelColumnName: "Price", maximumNumberOfIterations: 100));

 

        // 3. Train model

        var model = pipeline.Fit(trainingData);

 

        //***** Model Evaluation

        HouseData[] testHouseData =

        {

            new HouseData() { Size = 1.1F, Price = 0.98F },

            new HouseData() { Size = 1.9F, Price = 2.1F },

            new HouseData() { Size = 2.8F, Price = 2.9F },

            new HouseData() { Size = 3.4F, Price = 3.6F }

        };

 

        var testHouseDataView = mlContext.Data.LoadFromEnumerable(testHouseData);

        var testPriceDataView = model.Transform(testHouseDataView);

 

        var metrics = mlContext.Regression.Evaluate(testPriceDataView, labelColumnName: "Price");

        double rs = metrics.RSquared;

        double rmse = metrics.RootMeanSquaredError;

    }

 

Metrics Summary

Listed below is a summary of 6 various ML.NET Tasks and their metrics:

 

BinaryClassification	MulticlassClassification	Regression
Accuracy	Micro Accuracy	R-Squared
AUC	Macro Accuracy	MAE (Mean Absolute Error)
AreaUnderPrecisionRecallCurve	Log-Loss	MSE (Mean Squared Error)
F1-score	Log Loss Reduction	RMSE (Root Mean Square Error)

 

 

Clustering	Ranking	AnomalyDetection
Avg Distance	DCG (Discounted Cumulative Gains)	Area Under ROC Curve
Davies Boulding Index	Normalized DCG (Discounted Cumulative Gains)	Detection Rate At False Positive Count
NMI (Normalized Mutual Information)

 

 

Reference: https://learn.microsoft.com/en-us/dotnet/machine-learning/resources/metrics

Azure AI Content Safety Service

Microsoft introduced a new AI service called “Azure AI Content Safety Service” at the Build conference in May 2023.  This new service will inspect for questionable content in any of the following categories.

1. Violent content
2. Hateful content
3. Sexual content
4. Self-harm content

 

The Content Safety service is intended to protect customers’ web sites and social media apps from receiving questionable comments or images.

Content maybe text, images, audio, video, or a combination of items (i.e. multi-modal). 

 

Users can utilize filters to tweak the severity levels.  For example, an outdoor equipment provider may allow images of knives or guns uploaded to their social media, but a school or church may like to prevent those images. Filters are set to Medium by default and can be increased.  Turning the filter settings to be less restricted or turned off requires a written application to Microsoft to ensure the customer is trusted and low risk.

 

The AI Content Safety Service is built into Open AI and most Microsoft AI products.  It’s used internally at Microsoft as well in public products like Bing Chat. The purpose is to uphold responsible AI principles provided by Microsoft.

 

 

 

Code Example

1. Using the Azure AI Content Safety Service is accessible through the Azure portal. After logging into the portal, simply create a “Content Safety” Resource in an existing group or a new group.

 

2. Once the resource is created, the Keys and Endpoints will be accessible in the Resource Management pane

 

 

3. To access the Safety Content API, I created a console application with the following NuGet packages

 

 

4. The code will utilize an API call, using the key and endpoint from step #2

 

using Azure;

using Azure.AI.ContentSafety;

using Microsoft.Extensions.Configuration;

using System.Reflection;

 

 

namespace AIContenSafety.ConsoleApp

{

    internal class Program

    {

        static void Main(string[] args)

        {

            var config = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();

            string endpoint = config["AppSettings:endpoint"];

            string key = config["AppSettings:key"];

 

            string datapath = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "Images", "TestImage1.jpg");

 

            ImageData image = new ImageData();

            image.Content = BinaryData.FromBytes(File.ReadAllBytes(datapath));

 

            var request = new AnalyzeImageOptions(image);

 

            Response<AnalyzeImageResult> response;

            try

            {

                ContentSafetyClient client = new ContentSafetyClient(new Uri(endpoint), new AzureKeyCredential(key));

                response = client.AnalyzeImage(request);

            }

            catch (RequestFailedException ex)

            {

                Console.WriteLine("Analyze image failed.\nStatus code: {0}, Error code: {1}, Error message: {2}", ex.Status, ex.ErrorCode, ex.Message);

                throw;

            }

 

            Console.WriteLine("Hate severity: {0}", response.Value.HateResult.Severity);

            Console.WriteLine("SelfHarm severity: {0}", response.Value.SelfHarmResult.Severity);

            Console.WriteLine("Sexual severity: {0}", response.Value.SexualResult.Severity);

            Console.WriteLine("Violence severity: {0}", response.Value.ViolenceResult.Severity);

        }

    }

}

 

 

Testing the Application

Provided in the solution is a folder containing a test image (shown below), called TestImage1.jpg.  Naturally this image should be classified as violent content.

 

Running the Application

Executing the console application will load the test image specified above.  All results are posted in the console window showing the type of content violating the safety guidelines.  In addition, it shows the severity level of the content.

 

Additional Resources

Get started in Studio https://aka.ms/contentsafetystudio

Visit product page to learn more https://aka.ms/contentsafety

Read the eBook https://aka.ms/contentsafetyebook