What is a Computed Column?
A Computed Column is a column whose values are derived or computed by an expression. The expression might be a constant, function or combination of values from one or more columns in the same table.
The code samples provided are based on an expressions with emphasis for EF Core while provides a data provider sample also.
GitHub Source code for article
Sample from code samples to get a year from the column BirthYear.
Limitations and Restrictions
Microsoft docs:
- A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint. For example, if the table has integer columns a and b, the computed column a + b may be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed, because the value might change in subsequent invocations.
- A computed column cannot be the target of an INSERT or UPDATE statement.
-
SET QUOTED_IDENTIFIERmust be ON when you are creating or changing indexes on computed columns or indexed views. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).
Note
When a formula combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, the error Error validating the formula for column column_name. is returned. Use the CAST or CONVERT function to resolve the data type conflict.
Add a new computed column
Microsoft SSMS docs using SSMS (SQL-Server Management Studio)
Note this can be done with EF Core also. Given the following model
public partial class Contact
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime? BirthDate { get; set; }
public int? YearsOld { get; set; }
public string FullName { get; set; }
public int? BirthYear { get; set; }
}
Configuration for the model, see project Context OnModelCreating.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Contact>(entity =>
{
entity.Property(e => e.BirthDate)
.HasColumnType("date");
entity.Property(e => e.BirthYear)
.HasComment("Computes birth year from BirthDate")
.HasComputedColumnSql("(datepart(year,[BirthDate]))", false);
entity.Property(e => e.FullName)
.HasComputedColumnSql("(([FirstName]+' ')+[LastName])", false);
entity.Property(e => e.YearsOld)
.HasComment("Computes years old from BirthDate")
.HasComputedColumnSql("(datediff(year,[BirthDate],getdate()))", false);
});
OnModelCreatingPartial(modelBuilder);
}
Here is what the raw definition looks like in SSMS.
EF Core reading data from Contacts
First example reads all contacts while the second example asserts for contacts ready for retirement using the computed column YearsOld. Both examples display FirstName and LastName using the computed column FullName.
private static void AllContacts()
{
using var context = new Context();
var contacts = context.Contact.ToList();
foreach (Contact item in contacts)
{
Console.WriteLine($"{item.Id,-3}{item.FullName,-15}Is {item.YearsOld,-3}years old born {item.BirthYear}");
}
Console.WriteLine();
}
private static void ReadForRetirement()
{
using var context = new Context();
int yearsOld = 65;
var readyForRetirement = context.Contact.Where(contact => contact.YearsOld > yearsOld).ToList();
foreach (Contact item in readyForRetirement)
{
Console.WriteLine($"{item.Id,-3}{item.FullName,-15}Is {item.YearsOld,-3}years old born {item.BirthYear}");
}
Console.WriteLine();
}
Suppose a user interface displays FullName, we can use the computed column FullName to find the contact.
private static void FindByFullName()
{
var fullName = "Karen Payne";
using var context = new Context();
var contact = context.Contact.FirstOrDefault(item => item.FullName == fullName);
Console.WriteLine(contact.Id);
Console.WriteLine();
}
EF Core working with Version class
Computed columns are very powerful. Entity Framework Core with its fluent API allows them to be easily added. Before using computed columns in production databases run test in SSMS on larger datasets than you would normally run, determine if performance is acceptable along with testing with proper indices.
This code sample shows storing version information in a SQL-Server database table.
Since each part of the version are stored as int you can take those column values and create a Version object or use TheVersion column for display purposes.
Table definition
Incrementing version parts
To keep code clean we have extension methods in a class project.
public static class Extensions
{
public static Version IncrementMajor(this Version sender, int increment = 1)
=> new(sender.Major + increment, sender.Minor, sender.Build, sender.Revision);
public static Version IncrementMinor(this Version sender, int increment = 1)
=> new (sender.Major, sender.Minor + increment, sender.Build, sender.Revision);
public static Version IncrementBuild(this Version sender, int increment = 1)
=> new (sender.Major, sender.Minor, sender.Build + increment, sender.Revision);
public static Version IncrementRevision(this Version sender, int increment = 1)
=> new (sender.Major, sender.Minor, sender.Build, sender.Revision + increment);
}
Get a record
using var context = new Context();
ApplicationSettings firstApp = context.ApplicationSettings.FirstOrDefault();
Version version = new Version(firstApp!.TheVersion);
Increment a part
version = version.IncrementMajor(1);
firstApp.VersionMajor = version.Major;
Simple math example
In this example we are summing up UnitPrice * Quantity into computed column RowTotal
public partial class OrderDetailsConfiguration :
IEntityTypeConfiguration<OrderDetails>
{
public void Configure(EntityTypeBuilder<OrderDetails> entity)
{
entity.HasKey(e => e.OrderId);
entity.Property(e => e.RowTotal)
.HasComputedColumnSql("([Quantity]*[UnitPrice])", false)
.HasColumnType("numeric(29, 2)");
entity.Property(e => e.UnitPrice).HasColumnType("numeric(18, 2)");
entity.HasOne(d => d.Product)
.WithMany(p => p.OrderDetails)
.HasForeignKey(d => d.ProductId)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_OrderDetails_Products");
OnConfigurePartial(entity);
}
partial void OnConfigurePartial(EntityTypeBuilder<OrderDetails> entity);
}
See also
Required
Microsoft Visual Studio 2022 version 17.4 or higher
Source code
Clone the following GitHub repository.
NuGet packages
-
ConfigurationLibrary is used for obtaining connection strings from
appsettingsin both projects. This is a great solution when not using dependency injection. - Spectre.Console for enhanced console writting.
Computed Column Performance in SQL Server
In many cases, non-persistent computed columns put too much burden on the processor, resulting in slower queries and unresponsive applications. Fortunately, SQL Server provides several strategies for improving computed column performance. You can create persisted computed columns, index the computed columns, or do both.
👓 Continue reading...
Top comments (0)