CODEWITHSUNDEEP
sqlsql-server
choloboy
choloboy

Reputation: 815

When to add calculated custom columns to a query - right away or by using a join

I am trying to add two columns to the query below:

This is my query 

SELECT     SystemName, Caption, Label, Capacity, FreeSpace
FROM         CCS_Win32_Volume
ORDER BY SystemName, Caption

USE [CentralConfigurationStore]
GO

Would adding it to the query or doing it after and joining the two tables be the better approach?

If anyone could help me out with this it would be appreciated!

Upvotes: 1

Views: 50

Answers (3)

Alan Schofield
Alan Schofield

Reputation: 21703

I think the question was the approach, not the actual T-SQL...

Unless your query is used elsewhere and changing it might break something, then I would update your existing query. If it is used elsewhere and it's going to be a lot of work to update all the related processes/queries then I would create a separate query with the complete results. There seems little point in creating a query with just the calculated values in because, as you stated, you'll then have to join it back to your original query, which just seems like more work for nothing.

Anyway, that's just my personal opinion.

Regards,

Al.

Upvotes: 1

Dave Jemison
Dave Jemison

Reputation: 704

Just alias the computer values. Also, you should put the USE command first:

USE [CentralConfigurationStore]
SELECT     SystemName, Caption, Label, Capacity 
   , FreeSpace, freespace/capacity AS FreeSpacePercent
   , capacity-freespace AS UsedSpace
FROM         CCS_Win32_Volume
ORDER BY SystemName, Caption

Upvotes: -1

Joachim Isaksson
Joachim Isaksson

Reputation: 180997

Calculated fields will not add much overhead when you add them right into the query, since the data you're calculating on has already been read from disk.

SELECT SystemName, Caption, Label, Capacity, FreeSpace,
       100*freespace/capacity [% of free space],
       capacity-freespace [used space]
FROM CCS_Win32_Volume
ORDER BY SystemName, Caption

An SQLfiddle to test with.

Upvotes: 2

Related Questions