SQL with Manoj

In SQL Server you have an option to create Calculated Columns in a table other than normal columns. By saying Normal columns I meant that their values are stored physically with the table. But with Calculated Columns their values are not stored physically, instead they are re-calculated every time whenever they are referenced.

A Computed Column is nothing but an expression, which can be a:

1. Non Computed column

2. Constant

3. Function

4. or any combination of above of these by using operators.

These type of Computed Columns are Non-Persisted in nature.
 

Persisted Computed Columns: You can also make Computed columns values stored physically by using the PERSISTED keyword. This will calculate the column values initially while inserting a row, or later if there is any change made to the column that is part of its expression.
 

Finally a Computed Column:

– is computed from an expression that can use other columns in the same table.

– can be a noncomputed column name, constant, function with one or more operators.

– cannot be a subquery.

– cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition.

– used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED.

– can be used in SELECT list, WHERE clause, ORDER BY clause, etc.

– cannot be the target of an INSERT or UPDATE statement.
 

Check video on how to create Calculated Columns in a Table:

–> SQL Script used in the above demo:

CREATE TABLE [dbo].[Employee] (
	[EmployeeID]	int,
	[EmployeeName]	nvarchar(100),
	[Gender]		nchar(1),
	[DOB]			datetime,
	[DOJ]			datetime,
	[DeptID]		int
)
GO

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('JHON K', 'M', '1970-03-03', NULL, NULL)

INSERT INTO [dbo].[Employee] (EmployeeName, DOB)
VALUES ('Brock H', '1980-02-02')
GO

SELECT * FROM [dbo].[Employee]
GO

ALTER TABLE [dbo].[Employee]
ADD AGE AS (DATEDIFF(YY, DOB, GETDATE()))
GO

ALTER TABLE [dbo].[Employee]
ADD XYZ AS 'nothing'
GO

SELECT * FROM [dbo].[Employee]
GO


CREATE TABLE dbo.Products (
    ProductID	int IDENTITY (1,1),
	Qty			smallint,
	Price		money,
	TotalValue AS (Qty * Price)
)

INSERT INTO dbo.Products (Qty, Price)
VALUES (5, 200)

INSERT INTO dbo.Products (Qty, Price)
VALUES (3, 150)

select * from dbo.Products
GO


ALTER TABLE dbo.Products
DROP COLUMN TotalValue

ALTER TABLE dbo.Products
ADD TotalValue AS (Qty * Price) PERSISTED
GO

-- Final Cleanup
DROP TABLE [dbo].[Employee]
DROP TABLE dbo.Products
GO