Hi friends, in this article, we will be discussing about computed columns in SQL Server and see some relevant T-SQL examples.
What are Computed and Persisted Columns in SQL Server?
When it comes to database design in SQL Server, it is always recommended to create the database as flexible as possible. SQL Server provides several features for developers in order to be able to do so.
An example of such a feature are the “computed” and “persisted” columns.
As explained on MS Docs, computed columns’ values are computed by expressions that can use other columns in the same table.
Also, when using the PERSISTED keyword with a COMPUTED column, the data of the computed column is physically stored in the table.
Let’s see some code and try out this great feature!
An Example of Using Computed and Persisted Columns in SQL Server
First let’s create two tables representing product information.
Creating the Tables
Note the column with name “totalPrice”: It is a computed column which computes the total price (price + VAT).
CREATE TABLE Product1 ( id int, code varchar(50), price float, vatPerc float, totalPrice as (price+(price*(vatPerc/100))) ) GO
Note again the column with name “totalPrice”: It is the same computed column as in table Product 1 with the difference that it uses the PERSISTED keyword.
CREATE TABLE Product2 ( id int, code varchar(50), price float, vatPerc float, totalPrice as (price+(price*(vatPerc/100))) PERSISTED ) GO
Testing the Tables
Testing Table Product1:
INSERT INTO Product1 (id,code,price,vatPerc) SELECT 1, 'PRODUCT01',100,15 UNION ALL SELECT 2, 'PRODUCT02',200,15 GO
* I know, I could use a Table Value Constructor (Row Constructors) but I would prefer that my examples to be compatible with all versions of SQL Server as Table Value Constructors is one of the new programmability features in SQL Server 2008 🙂
Note that in the above INSERT statement I am not inserting any values for the “totalPrice” column. Though, when taking a look at the generated results, the totalPrice values are calculated on the fly!
SELECT * FROM Product1 GO
Output:
Similarly, testing table Product2 will work the same way with the only difference that this time, the calculated values for the “totalPrice” column will be physically stored in the table:
Testing Table Product2:
INSERT INTO Product2 (id,code,price,vatPerc) SELECT 1, 'PRODUCT03',300,15 UNION ALL SELECT 2, 'PRODUCT04',400,15 GO SELECT * FROM Product2 GO
Output:
* Note: By the time you are using a Computed column you cannot explicitly insert data into it. The data is computed based on the computed column’s definition (expression).
Learn more about SQL Server Development – Enroll to our Course!
Enroll to our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link) and sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
Featured Online Courses:
- SQL Server 2022: What’s New – New and Enhanced Features
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
Read Also:
- SQL Server 2022: What’s New – New and Enhanced Features (Course Preview)
- SQL Server 2022 Generally Available!
- An existing History Table cannot be specified with LEDGER=ON – How to Resolve
- What is Data Management and why it is Important?
- What is Data Security and which are its Main Characteristics?
- Data Security vs. Data Privacy
- What are NoSQL Databases?
- Differences Between Batch and Streaming Data
- What is Data Compliance within the Data Management Scope?
- How to Connect to SQL Server Databases from a Python Program
- How to Resolve: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)
- Useful Python Programming Tips
- Main Data Structures in Python
- Working with Python on Windows and SQL Server Databases (Course Preview)
- How to Write to a Text File from a C++ Program
- How to Establish a Simple Connection from a C# Program to SQL Server
- The timeout period elapsed prior to obtaining a connection from the pool
- Closing a C# Application (including hidden forms)
- Changing the startup form in a C# project
- Using the C# SqlParameter Object for Writing More Secure Code
- Cannot implicitly convert type ‘string’ to ‘System.Windows.Forms.DataGridViewTextBoxColumn
Subscribe to our newsletter and stay up to date!
Subscribe to our YouTube channel (SQLNetHub TV)
Check our latest software releases!
Check our eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© SQLNetHub
Artemakis Artemiou is a seasoned Senior Database and AI/Automation Architect with over 20 years of expertise in the IT industry. As a Certified Database, Cloud, and AI professional, he has been recognized as a thought leader, earning the prestigious Microsoft Data Platform MVP title for nine consecutive years (2009-2018). Driven by a passion for simplifying complex topics, Artemakis shares his expertise through articles, online courses, and speaking engagements. He empowers professionals around the globe to excel in Databases, Cloud, AI, Automation, and Software Development. Committed to innovation and education, Artemakis strives to make technology accessible and impactful for everyone.