SQL Server Performance

Computed value of a View

Discussion in 'General Developer Questions' started by kel, Jun 11, 2003.

  1. kel New Member

    I have a view that has 2 columns. The first column is associated with a function for the Units. The second column calculates the Market Value:

    View
    ====
    Col1: Unit = get_number_of_units()
    Col2: MV = get_number_of_units() * get_unit_value()

    I need to call get_number_of_units() twice in the view. Is it possible to changes Col2 to something like: MV = Col1 * get_unit_value()?

    Is get_unit_value() being called if I do Select Col1 from View?

    Thanks.

  2. bambola New Member

    No it is not.

    Try this:


    USE northwind
    GO

    CREATE function dbo.calc_ok(@val int)
    returns int
    as
    begin
    return @val - 2
    end

    GO

    CREATE function dbo.calc_err(@val int)
    returns int
    as
    begin
    return 10/@val
    end

    GO

    CREATE VIEW test_calc
    as
    SELECT EmployeeID
    , dbo.calc_ok(EmployeeID) calcok
    , dbo.calc_err(dbo.calc_ok(EmployeeID)) calc_err
    FROM dbo.Employees

    GO

    now run this 2 select statements.

    This one will include EmployeeID 2 and will result
    a division by 0, therefore an error.
    Code:
    SELECT TOP 2 * FROM test_calc ORDER BY EmployeeID
    
    This one won't.
    SELECT TOP 2 EmployeeID FROM test_calc ORDER BY EmployeeID
    
    Bambola.
     

Share This Page