create database demo1
go

use demo1
go


drop table readings
drop function FahrenheitToCentigrade
drop function KilogramsToPounds
drop assembly MetricConverter
go

-- create the assembly
CREATE ASSEMBLY MetricConverter
FROM 'C:\types\metricconverter.dll'
go

  /*
  public static double FahrenheitToCentigrade(double v)
  public static double CentigradeToFahrenheit(double v)
  public static double MilesToKilometers(double v)
  public static double KilometersToMiles(double v)
  public static double PoundsToKilograms(double v)
  public static double KilogramsToPounds(double v)
  */

create function FahrenheitToCentigrade (@a float)
returns float
external name MetricConverter.[MetricFunctions.MetricConverter].FahrenheitToCentigrade
go

create function KilogramsToPounds (@a float)
returns float
external name MetricConverter.[MetricFunctions.MetricConverter].KilogramsToPounds
go

-- use it
declare @x float
declare @y float
set @x = -40
set @y = dbo.FahrenheitToCentigrade(@x)
print @y
go

create table readings(
 ReadingDate datetime not null, 
 City        varchar(20) not null, 
 HiTemp_Far  real, 
 HiTemp_Cen  as dbo.FahrenheitToCentigrade(hitemp_far) PERSISTED
)
go

ALTER TABLE readings WITH NOCHECK 
ADD CONSTRAINT PK_ReadingDate_City PRIMARY KEY CLUSTERED
(ReadingDate, City) 
go

CREATE INDEX IDX_hitemp_far ON readings(hitemp_far)
go

CREATE INDEX IDX_hitemp_cen ON readings(hitemp_cen)
go