How to create a computed Column in SQL Server

In this post, we will learn how to create a computed column in SQL and how to define the calculation logic for this computed column.

A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs

SQL : PERSISTED Computed Column and Performance Improvement

Here is an example to calculate the DiscountedAmount for each order

DiscountedAmount = OrderAmount * 25%

Syntax: Create SQL Computed Column on Create Table:

The below SQL script creates a table with Computed Column ‘DiscountedAmount’ and field value will be calculated based on other column

CREATE TABLE Orders (

    OrderID int NOT NULL,

    OrderNumber int NOT NULL,

    StatusID int, 

    OrderAmount Decimal(10,2),

    OrderDate date DEFAULT GETDATE(),

    DiscountedAmount AS (OrderAmount * .25),

    PRIMARY KEY (OrderID),   

);

When you fetch records from table, the Computed field DiscountedAmount will have calculated value based on defined expression (OrderAmount * .25) in schema.

----Inserts value in the table

Insert dbo.Orders(OrderID,OrderNumber,StatusID,OrderAmount)

Select 1, 100, 1, 100

Insert dbo.Orders(OrderID,OrderNumber,StatusID,OrderAmount)

Select 2, 101, 1, 300

----Display the rows in the table

Select * from dbo.Orders

Output:

Syntax: Create SQL Computed Column on Alter Table:

The below SQL script creates a Computed Column ‘DiscountedAmount’ on an existing table

ALTER TABLE dbo.Orders ADD DiscountedAmount AS (OrderAmount * .25)

Syntax: Drop SQL Computed Column:

The below SQL script delete a Computed Column ‘DiscountedAmount’ from table

ALTER TABLE dbo.Orders DROP COLUMN DiscountedAmount

Date/Time to String Conversion by using Convert ( ) function

In this blog, we will learn how to convert the date/time into string by using Convert () SQL function and explore the different style of date/time format in conversion.

Convert () is used to convert a given value of one data type to another data type and it works same as Cast () function except it allows third parameter for date /time format style eg. yyyy-mm-dd, dd mm yyyy etc.

Here is an example for Converting from datetime with default style (0, 100) and without passing style options and all three return same output (mon dd yyyy hh:miAM (or PM))

Select Convert(varchar,getdate())

Select Convert(varchar,getdate(), 100)

Select Convert(varchar,getdate(), 0)

Output:

Jul 31 2019  4:18PM

Two Digit Years Style (yy):

The below sql script convert the datetime into date string with 2 digits year part

Select Convert(varchar,getdate(), 1)

Output:

07/31/19

Select Convert(varchar,getdate(), 2)

Output:

19.07.31

Below is a list of formats and an output

Style /Format	Output
1	mm/dd/yy
2	yy.mm.dd
3	dd/mm/yy
4	dd.mm.yy
5	dd-mm-yy
6	dd mon yy
7	Mon dd, yy
8	hh:mi:ss
10	mm-dd-yy
11	yy/mm/dd
12	yymmdd
14	hh:mi:ss:mmm

Four Digit Years Style (yyyy):

The below sql script convert the datetime to date string with 4 digits year part

Select Convert(varchar,getdate(), 101)

Output:

07/31/2019

Select Convert(varchar,getdate(), 102)

Output:

2019.07.31

Below is a list of formats and an output

Style /Format	Output
100	mon dd yyyy hh:miAM
101	mm/dd/yyyy
102	yyyy.mm.dd
103	dd/mm/yyyy
104	dd.mm.yyyy
105	dd-mm-yyyy
106	dd mon yyyy
107	Mon dd, yyyy
108	hh:mi:ss
109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yyyy
111	yyyy/mm/dd
112	yyyymmdd
113	dd mon yyyy hh:mi:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120	yyyy-mm-dd hh:mi:ss(24h)
121	yyyy-mm-dd hh:mi:ss.mmm(24h)

String to Date/Time Conversion by using Convert ( ) function

In this blog, we will learn how to convert the string into Date/Time type by using Convert () SQL function.

Convert () is used to convert a given value of one data type to another data type.

Convert into DateTime type by using Convert () function:

The below SQL script is used to convert date string into DateTime type

Select CONVERT (Datetime,'02/12/2019')

Output:

2019-02-12 00:00:00.000

If input value is not valid date sting, it throw below exception

Example:

Select CONVERT (Datetime,'12S2019')

Exception:

Conversion failed when converting date and/or time from character string.

If input string is date string but it is invalid date, it throw below exception

Example: 31th day is invalid for February month

Select CONVERT (Datetime,'02/31/2019')

Exception:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Convert into Date type by using Convert () function:

The below SQL script is used to convert input string into Date type (only date information)

Select CONVERT (Date,'02/18/2019')

Output:

2019-02-18

Convert into time type by using Convert () function:

The below SQL script is used to convert input string into time type

Select CONVERT (Time,'02/18/2019 09:02')

Output:

09:02:00.0000000

Select CONVERT (Time,'09PM')

Output:

21:00:00.0000000

String to Date/Time Conversion by using Cast ( ) function

In this blog, we will learn how to convert the string into Date/Time type by using Cast () SQL function.

Cast () is used to convert a given value of one data type to another data type and Convert () function works almost same as Cast () function except syntax different.

Convert into DateTime type by using Cast () function:

The below SQL script is used to convert date string into DateTime type

Select CAST ('12/31/2019' AS DateTime)

Output:

2019-12-31 00:00:00.000

If input value is not valid date sting, it throw below exception

Example:

Select CAST ('12S2019' AS DateTime)

Exception:

Conversion failed when converting date and/or time from character string.

If input string is date string but it is invalid date, it throw below exception

Example: 31th day is invalid for February month

Select CAST ('02/31/2019' AS DateTime)

Exception:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Convert into Date type by using Cast () function:

The below SQL script is used to convert input string into Date type (only date information)

Select CAST ('02/18/2019' AS date)

Output:

2019-02-18

Convert into time type by using Cast () function:

The below SQL script is used to convert input string into time type

Select CAST ('02/18/2019 09:02' AS time)

Output:

09:02:00.0000000

Select CAST ('09pm' AS time)

Output:

21:00:00.0000000

SQL DEFAULT Constraint

SQL DEFAULT Constraint provides a default value to a column when adding new record and no value provided for a column.

Here is an example:  OrderDate Column has default value  GetDate() SQL function. If user doesn’t provide value for OrderDate, by default it will take today date in case of blank

Syntax: Create DEFAULT Constraint on Create Table:

The below SQL script creates a Default constraint for OrderDate Column

CREATE TABLE Orders (

    OrderID int NOT NULL,

    OrderNumber int NOT NULL,

    StatusID int, 

    OrderAmount Decimal(10,2),

    OrderDate date DEFAULT GETDATE(),

    PRIMARY KEY (OrderID),

    CONSTRAINT FK_OrderOrderStatus FOREIGN KEY (StatusID)

    REFERENCES OrderStatus(StatusID)

);

Syntax: Create DEFAULT Constraint on Alter Table:

The below SQL script creates a Default constraint for OrderDate Column of a table, which is already created

ALTER TABLE dbo.Orders

ADD CONSTRAINT df_OrderDate

DEFAULT GETDATE() FOR OrderDate;

Syntax: Drop DEFAULT Constraint:

The below SQL script uses to drop a Default constraint from table

ALTER TABLE Orders

DROP CONSTRAINT df_OrderDate;

SQL Check Constraint

A SQL Check Constraint is defined a condition on a column of table that must satisfy then it allows to insert or update for a column.

Here is an example: OrderAmount must be positive value

Syntax: Create Check Constraint on Create Table:

CREATE TABLE Orders (

    OrderID int NOT NULL,

    OrderNumber int NOT NULL,

    StatusID int, 

    OrderAmount Decimal(10,2) Check(OrderAmount > 0) NOT NULL

    PRIMARY KEY (OrderID),

    CONSTRAINT FK_OrderOrderStatus FOREIGN KEY (StatusID)

    REFERENCES OrderStatus(StatusID)

);

Basically Check Keyword is used to apply Check Constraint on a column and usually it is a logically expression

Check(OrderAmount > 0) NOT NULL

Syntax: Create Check Constraint on Alter Table:

The below SQL script a Check Constraint on table, which is already created

ALTER TABLE dbo.Orders

ADD CONSTRAINT OrderAmount_Ckeck Check(OrderAmount > 0);

Syntax: Drop Check Constraint:

The below SQL is used to drop the check constraint from Table

ALTER TABLE Orders

DROP CONSTRAINT OrderAmount_Ckeck;