set nocount on
go
CREATE TABLE CaseDetails
(CaseId INT IDENTITY(1,1) primary key, 
QTY NUMERIC(14,4), 
weightedQty NUMERIC(14,4), 
ShipmentId INT,
Total as Qty + WeightedQty)
go
raiserror('First Insert', 10, 1) with nowait
go
insert into dbo.CaseDetails(qty, weightedqty, shipmentid)
select 200, 300, 1
from common.dbo.bignumbers
where number between 1 and 15000
go
raiserror('Second Insert', 10, 1) with nowait
go
insert into dbo.CaseDetails(qty, weightedqty, shipmentid)
select 200, 300, numbers.number
from common.dbo.numbers
cross join common.dbo.numbers n2
where numbers.number between 2 and 10000
go
raiserror('Index', 10, 1) with nowait
go
create index IDX_CaseDetails_TotalQty on dbo.casedetails(shipmentid)
include (qty, weightedqty, total)
go
set statistics io on
set statistics time on

print 'First Method'

select qty, weightedqty, qty + weightedqty
from dbo.casedetails
where shipmentid = 1
/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
Table 'CaseDetails'. Scan count 1, logical reads 82, 
physical reads 0, read-ahead reads 0, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 231 ms.
*/
print 'Second Method'

declare @Total int

select @total = qty + weightedqty
from dbo.casedetails
where shipmentid = 1

select qty, weightedqty, @total
from dbo.casedetails
where shipmentid = 1
/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
Table 'CaseDetails'. Scan count 1, logical reads 82, 
physical reads 0, read-ahead reads 0, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 8 ms.
Table 'CaseDetails'. Scan count 1, logical reads 82, 
physical reads 0, read-ahead reads 0, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 230 ms.
*/
print 'Third Method'

select qty, weightedqty, total
from dbo.casedetails
where shipmentid = 1
/*
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
Table 'CaseDetails'. Scan count 1, logical reads 82, 
physical reads 0, read-ahead reads 0, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 226 ms.
*/