Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate the creation of computed columns and check constraint in table definition (CREATE TABLE).

/*

SQL CASE function is used to define computed columns (Salutation, Deductions)

in the create table statement.

The syntax for a computed column is: ColAlpha AS ....

A computed column is updated instantenously after data insert or update.

*/

USE AdventureWorks;

GO

CREATE TABLE PartTimeEmployee (

  PartTimeEmployeeID INT    IDENTITY ( 1 , 1 )

  CONSTRAINT PartTimeEmployeePK PRIMARY KEY,

  FirstName          VARCHAR(50)    NOT NULL,

  LastName           VARCHAR(50)    NOT NULL,

  Gender             CHAR(1)    NOT NULL CHECK (Gender in ('F','M')),

  Married            BIT    NOT NULL,

-- SQL computed column

  Salutation         AS CASE

    when Gender = 'M' then 'Mr.'

    when Gender = 'F' and Married = 0 then 'Miss'

    when Gender = 'F' and Married = 1 then 'Mrs.'

    else ''

  end,

  Salary             SMALLMONEY    NOT NULL,

-- SQL computed column

  Deductions         AS CASE

    When Salary < 20000 then .10 * Salary

    when Salary between 20000 and 49999 then .22 * Salary

    when Salary between 50000 and 69999 then .34 * Salary

    else .40 * Salary

  end,

  EmploymentStart    SMALLDATETIME    NOT NULL

      CONSTRAINT EmploymentStartDF DEFAULT getdate(),

  IsEmploymentEnded  BIT    NOT NULL

      CONSTRAINT IsEmploymentEndedDF DEFAULT 0,

  EmploymentEnd      SMALLDATETIME    NULL

  )

GO

INSERT PartTimeEmployee (FirstName, LastName, Gender,

Married, Salary, EmploymentStart)

SELECT 'Roger', 'Vivamore', 'M', 0, 44.00, '2015-02-01'

SELECT * FROM PartTimeEmployee

GO

/* Results

PartTimeEmployeeID      FirstName   LastName    Gender      Married     Salutation  Salary      Deductions  EmploymentStart   IsEmploymentEnded EmploymentEnd

1     Roger Vivamore    M     0     Mr.   44.00 4.400000    2015-02-01 00:00:00     0      NULL

*/

-- Cleanup

DROP TABLE PartTimeEmployee

GO
------------

------------

-- UDF as Computed Column - computed column based on user-defined function

------------

USE tempdb;

GO

CREATE TABLE ShippingRate (ID int identity(1,1) primary key,

  OrderValueMax money,

  ShippingRate smallmoney,

  ModifiedDate date default (CURRENT_TIMESTAMP));

GO

INSERT ShippingRate(OrderValueMax, ShippingRate) VALUES

(0.0, 0.15),

(100.0, 0.10),

(1000.0, 0.08),

(10000.0, 0.06),

(100000.0, 0.04)

GO

CREATE FUNCTION ufnShippingFee (@OrderValue money)

RETURNS money

AS

BEGIN

      DECLARE @Fee money

      SELECT @Fee=@OrderValue * min(ShippingRate)

      FROM ShippingRate where @OrderValue > OrderValueMax

      RETURN (@Fee)

END

GO

CREATE TABLE Orders (OrderID int identity(1,1) primary key,

  CustomerID int,

  ProductID int,

  Qty int,

  SellUnitPrice money,

  TotalWithShipping AS Qty * SellUnitPrice+

                       dbo.ufnShippingFee (Qty * SellUnitPrice),

  OrderDate datetime default (getdate()));

GO

INSERT Orders (CustomerID, ProductID, Qty, SellUnitPrice) VALUES

(1,1,2,30),

(1,1,12,30),

(1,1,12,130),

(2,2,2,30),

(3,12,200,30)

GO

SELECT * FROM Orders ORDER BY OrderID

GO

/*

*/ 

------------