Skip to content
Donner's Daily Dose of Drama
Donner's Daily Dose of Drama
  • The Good
    • Blogging
    • Consumer Protection
    • Environment
    • Ethics
    • Geek’s Home
    • Lisa Lanett
    • Medfield
    • Music
    • Parenting and Technology
    • Travel
    • wow
  • The Bad
    • Business
    • Ebay
    • Investment
    • Job search
    • Personal Finance
    • Politics
  • The Ugly
    • Information Technology
      • Business Intelligence
      • Content Management
      • Free Software
      • I18N and L10N
      • Java
      • Open Source
      • Mobile Devices
      • Open Source Business Intelligence
      • OSBI
      • SDA
      • Security
      • Smartphone
      • Software Best Practices
      • Software Engineering
      • SQL Server
      • Streaming Media
      • Web
    • Austria
    • Fiction
    • Hardware
    • iPod
    • Miscellaneous
    • Uncategorized
    • Video
    • Weekend Warrior
Donner's Daily Dose of Drama

Using SQL Server Column Aliases

Christian Donner, March 14, 2008January 12, 2013

When you have a query that does complex calculations, maybe even has function calls in the select list

select
       costly_function_A() as result_A,
       costly_function_B() as result_B
from
       table_T

and you want to return the result of the calculation or function call, but also manipulate it further and return the result of this operation as well,

select
       costly_function_A() as result_A,
       costly_function_B() as result_B
       result_A/result_B as result_ratio
from
       table_T

then you face the following dilemma:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'result_A'.

It is not allowed to reference named colums of a result set in the same query. The trivial workaround is to do the calculation or function call twice in the query:

select
       costly_function_A() as result_A,
       costly_function_B() as result_B
       costly_function_A()/costly_function_B() as result_ratio
from
       table_T

This would work but is not optimal because it nearly doubles the execution time (the same functions are evaluated twice). So what does SQL Server offer in terms of query-writing tricks that let us get around this issue?

There are 2 choices – neither is particularly elegant.

First, we can put the costly stuff into a derived table where it gets executed only once, and do the secondary calculation in an outer query:

select
       costly_T.result_A,
       costly_T.result_B
       costly_T.result_A/costly_T.result_B as result_ratio
from
(
       select
              costly_function_A() as result_A,
              costly_function_B() as result_B
       from
              table_T
)
as costly_T

Or we can use a view. If you do not already use views, don’t introduce one, but go with the derived table. If you do have views, you may already have the necessary layer of abstraction that is needed here.

Related Posts:

  • My USPS Certified Mail Experience Explained
  • Enphase Envoy Local Access
  • Amazon threatens customer of 26 years
  • The Voip.ms SMS Integration for Home Assistant
  • Computer Build 2025

SQL Server SQL

Post navigation

Previous post
Next post

Leave a Reply

Your email address will not be published. Required fields are marked *

Pages

  • About
  • Awards
    • TechnoLawyer
  • Contact Christian Donner
  • Project Portfolio
  • Publications
  • Speaking Engagements

Recent Comments

  • Christian Donner on Sealing a leaky cast-iron fireplace chimney damper
  • Eric on Sealing a leaky cast-iron fireplace chimney damper
  • Christian Donner on Contact Christian Donner
  • Max on Contact Christian Donner
  • Christian Donner on Contact Christian Donner

Tags

AHCI Amazon Android ASP.Net AT&T Droid Drupal email Error failure featured firmware Garmin Godaddy Google honda Internet Explorer 8 iPhone Lenovo Lisa Lanett Modules NAS Nexus One Paypal Performance Privacy QNAP raid RS-407 sauna Security spam SQL SR3600 Synology T-Mobile T430s transmission tylö Verizon Virus VMWare Windows 7 windows 8.1 Windows Mobile
  • About
  • Awards
    • TechnoLawyer
  • Contact Christian Donner
  • Project Portfolio
  • Publications
  • Speaking Engagements
©2025 Donner's Daily Dose of Drama | WordPress Theme by SuperbThemes