Save a step: Using Computed Columns in a Table Variable (or Temp Table)

One of the databases that I support reports all money values in both the local currency and US Dollars.  Very often we will do all of our calculations in a table variable using the local currency and then calculate USD equivalents at the very end.

With this methodology, adding a new money field to a table variable requires making four code changes – adding the local money field, adding the USD money field, writing the code to retrieve the appropriate data for the local money field, and then modifying the final update or select statement to include the USD equivalent calculation.

I recently realized that we could save a step and do our FX calculations “automatically” by just including the USD equivalent columns in our table variables as computed columns.

If you are already using temporary tables or table variables in a “Set By Agonizing Set” SBAS style (perhaps I just coined this?), using this technique may allow you to keep your code a bit cleaner.

Here is an example:

DECLARE @FXRatesToUSD TABLE (

      Currency VARCHAR(3) NOT NULL PRIMARY KEY,

      RateToUSD FLOAT);

     

--Approximate FX Rates in October 2011

INSERT INTO @FXRatesToUSD

      VALUES ('USD',1.0), ('GBP',0.62), ('NOK',5.43);

 

DECLARE @Example TABLE (

      VehicleID INT NOT NULL PRIMARY KEY,

      Currency VARCHAR(3) NULL,

      PriceLocal MONEY NULL,

      FXRate FLOAT NULL,

       --computed column in the table variable

      PriceUSD AS CAST(ROUND(PriceLocal / FXRate,-3) AS MONEY)

      );

 

--Insert sample data

INSERT INTO @Example (VehicleID, Currency, PriceLocal)

      VALUES (1, 'USD', 45000),

            (2, 'USD', 14000),

            (3, 'USD', 31000),

            (4, 'GBP', 20000),

            (5, 'NOK', 190000);

 

--take a look at the table

SELECT 'FX Rates not yet set' AS [example], * FROM @Example;

 

--merge in our FX rates

UPDATE e1

      SET e1.FXRate = fx.RateToUSD

      FROM @Example e1

      INNER JOIN @FXRatesToUSD AS fx ON fx.Currency = e1.currency;

 

--now that the FX rates are present, our USD prices are available

SELECT 'FX Rates are set' AS [example], * FROM @Example;

 

--discount one of the cars

UPDATE @Example SET PriceLocal = 18000 WHERE VehicleID = 4;

 

--You should see that both prices are updated now for vehicle 4.

SELECT 'Updated GBP and USD price for Vehicle 4' AS [example],

* FROM @Example;

 

One of the only problems with this approach is that it does not seem possible to “chain” computed columns.  If I added a maximum USD discount field such as this to the table:

MaxDiscountUSD AS PriceUSD * 0.1

I would unfortunately get this error.

Msg 1759, Level 16, State 0, Line 11

Computed column 'PriceUSD' in table '@Example' is not allowed to be used in another computed-column definition.

In this case, it would seem that this technique should only be used for very simple one-pass cases or in conjunction with deterministic scalar UDFs that modularize the underlying logic.

The Recursive CTE

(Yet another boring org chart example – except this one has multiple roots)

One of my apps has a hierarchial org chart in its database.  We don’t use the hierarchyid data type for this table.  Instead, each row in the table just has a unique org ID and the org ID of the parent org which should exist in the same table.  The ParentOrgID field is NULL in the rows for the root orgs.  Notice I said root orgs – plural.  We have an odd situation with this database where there’s actually two different companies’ trees stored in this single table.  I recently had a requirement to pull out all the orgs for both companies and say what the root was, in addition to presenting how deep in the hierarchy it lived.  Here’s how I did it.

--Example source table

DECLARE @Orgs AS TABLE (

      OrgID INT PRIMARY KEY,

      ParentOrgID INT NULL,

      OrgName VARCHAR(20) NOT NULL

);

 

--Set up some sample data

INSERT INTO @Orgs (OrgID, ParentOrgID, OrgName) VALUES

      (1,NULL,'Company A (Root)'),

      (2,1,'Marketing'),

      (3,1,'Finance'),

      (4,3,'Accounting'),

      (5,2,'Sales'),

      (6,1,'Research'),

      (7,NULL,'Company B (Root)'),

      (8,7,'Production'),

      (9,7,'Development'),

      (10,7,'Processing'),

      (11,10,'Shipping'),

      (12,3,'IT'),

      (13,8,'Industrial Relations'),

      (14,2,'Branding'),

      (15,11,'Intl Shipping'),

      (16,12,'DBAs'),

      (17,12,'Server Ops'),

      (18,12,'Desktop Support');   

 

--Declare some constants pointed at my root orgs (could also use a config table)

DECLARE @ARoot INT;

DECLARE @BRoot INT;

SELECT @ARoot = OrgID FROM @Orgs WHERE OrgName = 'Company A (Root)';

SELECT @BRoot = OrgID FROM @Orgs WHERE OrgName = 'Company B (Root)';

 

WITH AllOrgs AS  --setting up the CTE

(SELECT OrgID,

      CASE WHEN OrgID = @ARoot then 'A'  --This case block is my way of dealing with

            WHEN OrgID = @BRoot then 'B'  --aliasing the company names as a code

            ELSE '' END as [RootCompany],

            1 as [OrgLevel],             --C.S. students would put a 0 here...

            OrgName, ParentOrgID FROM @Orgs

      Where OrgID = @ARoot OR OrgID = @BRoot --This is the query for the first row

            UNION ALL

      SELECT c.OrgID, p.RootCompany, p.OrgLevel + 1, c.OrgName, c.ParentOrgID

            from AllOrgs p

            INNER JOIN @Orgs c

            on c.ParentOrgID = p.OrgID  --query for remaining rows

)

SELECT OrgID, RootCompany, OrgLevel,Orgname, ParentOrgID

FROM AllOrgs Order by RootCompany, OrgLevel, OrgName; --returns the CTE data

 

Inside the CTE are two SELECT statements married by a UNION ALL.  The first one is basically for the first line of data to return (or first lines in this case, since there are two possible roots).

The second query returns the data for each successive row, recursively.  SQL Server will automatically quit recursion when a row is null so you don’t have to worry about closing the loop yourself somehow as long as you do the join correctly and there are no circular references.  In the second query, “p” aliases the CTE itself which allows us to read the parent row’s data and do some basic operations such as adding one to its OrgLevel value.  Note that as with all UNION statements in SQL Server, you only have to alias the column names in the top query – column names in later queries are ignored.

Finally, I select the data I want with my desired sorting.  CTEs self-destruct after use (note the semicolon after the select, but not after the closing parenthesis after the CTE setup), so if you need to do multiple operations on the results, insert them into a temp table first.

Renumbering Rows in a Table Variable (or Table/Temp Table)

I was recently working on a data cleanup problem where I had to do lots of comparisons of one row to the next row and I was trying to do my best to avoid using cursor for this.  I was using the old trick of having an IDENTITY() field and doing a self-join where the identity field in the main table = the identity field in the “comparison” table –1.  This was working great until I had to do a second set of deletes from my temp table and realized that some of my rows were now missing and therefore my IDENTITY() numbers weren’t always sequential anymore.

I found a great trick from MSDN (here) on how to get around this.  Below I’m providing some sample code to demonstrate this trick.  I believe this should work on SQL Server 2005 or higher.  The trick is by the comment called “Renumber the RowIDs”.  It involves using a CTE and the ROW_NUMBER() window function as part of an UPDATE statement.

Be sure to use semicolons after your statements when you start using CTEs or MERGE statements as SQL Server can start getting confused if the code is ambiguous.

--Setup a table variable with some data that has some definite duplicates and some

--“not so sure” duplicates – this happens to be someone’s job history.

DECLARE @SomeData TABLE

       (RowID INT UNIQUE,

        EmpID VARCHAR(8),

        PositionTitle VARCHAR(40),

        PositionEffDate DATETIME,

        PositionID VARCHAR(8));

        

INSERT INTO @SomeData VALUES

       (1,'00000012','Manager','1/1/2000','ABC123'),

       (2,'00000012','Manager','1/1/2000','ABC123ZZ'),

       (3,'00000012','Sr. Manager','1/1/2002','ABC125'),

       (4,'00000012','Sr. Manager','1/1/2002','ABC125'),

       (5,'00000012','Sr. Manager','1/1/2002','ABC125ZZ'),

       (6,'00000012','Director','1/1/2006','ABC126'),

       (7,'00000012','Director','1/1/2006','ABC126'),

       (8,'00000012','Sr. Director','1/1/2009','ABC129ZZ');

      

SELECT 'Has Dups and possibly bad rows' as [Description],

       * FROM @SomeData;

 

 

--this gets rid of rows that are certainly dups - same position title,

--  effective date, and position ID.

DELETE FROM @SomeData WHERE RowID IN (

       SELECT compare.RowID

              FROM @SomeData main

                     LEFT OUTER JOIN @SomeData compare on main.EmpID = compare.EmpID

                           AND main.RowID = compare.RowID -1

                     where main.PositionTitle = compare.PositionTitle

                           AND main.PositionID = compare.PositionID

                           AND main.PositionEffDate = compare.PositionEffDate);

                                        

SELECT 'Pure dups removed, some possibly bad rows, split row ids' as [Description],

       * FROM @SomeData;

 

--Renumber the RowIDs

WITH newPH AS(

       SELECT RowID, ROW_NUMBER() OVER(Order By RowID ASC) as [newRowID] FROM @SomeData)

UPDATE newPH

       SET RowID = newRowID;

 

SELECT 'Pure dups removed, some possibly bad rows, fixed row ids' as [Description],

       * FROM @SomeData;

--this gets rid of rows that are still dups - same position title,

--  effective date, keeping the first row that matches

DELETE FROM @SomeData WHERE RowID IN (

       SELECT compare.RowID

              FROM @SomeData main

                     LEFT OUTER JOIN @SomeData compare on main.EmpID = compare.EmpID

                           AND main.RowID = compare.RowID -1

                     where main.PositionTitle = compare.PositionTitle

                           AND main.PositionEffDate = compare.PositionEffDate);

 

--See that the "Sr. Director" row is kept since that has a different title than the

-- prior row.  The second Sr. Manager row (with the ZZ code in position ID) is removed.

SELECT 'Fixed up' as [Description],

       * FROM @SomeData;