Computed columns can be a great tool if you want to add a value to a table that’s dependent on other columns in the table, and you don’t want to maintain it on its own every time the source columns change. In a view, these can provide a single, consistent calculation to the end users – say, for a
-- Set up a view with the same columns and calculation errors in it
-- The first calculation will always work
-- The second gives a divide by zero error on every 10th row
CREATE VIEW SourceObject AS
SELECT object_id AS KeyColumn1,
column_id as KeyColumn2,
object_id - column_id as CalcColumn1,
(object_id - (column_id % 10)) as CalcColumn2
FROM msdb.sys.columns
Now that it’s set up, we can try selecting all the rows from the view we just created, and we’ll see about 100 rows output, and then the query will stop with a “Divide by zero” error:
SELECT * FROM SourceObject
The calculation in this query is pretty straightforward, and you can see which rows are causing a problem (where column_id is divisible by 10), but what if it was more complicated? The problem is that SQL doesn’t display the row that had a problem – it stops on the row before the problem, so finding the row with the error is bit more difficult. If there were multiple columns involved in the calculation, or different combinations of values that could result in an error? Tracking down the rows causing an error can be difficult – you have to find all the possible conditions that could cause an error, and then query for each of them.
This script will allow you to find all the rows in a view with a calculation error, all at once. It uses a cursor to pull the rows from the view one at a time, test the calculation, and then write any errors it finds to a table where you can see the rows that are causing problems. Using a CURSOR generally isn’t ideal, but in this case, it’s the only way to react to a bad calculation on a row-by-row basis and deal with it.
The script can use two key values from your view – they’re called KeyColumn1 and KeyColumn2 – and you can modify the script to name them whatever you want, or just a single a value if that makes more sense in your scenario. It also supports two computed columns – CalcColumn1 and 2 – though again, it could be changed to just check a single column.
-- Set up variables
DECLARE @KeyColumn1 INT,
@KeyColumn2 INT,
@CalcColumn1 INT,
@CalcColumn2 INT
DECLARE @CurrentRow BIGINT
SET @CurrentRow = 1
-- Set up a place to hold key values for rows that work
SELECT TOP 0 KeyColumn1, KeyColumn2
INTO #WorkingRows
FROM SourceObject
-- Set up a place to hold errors for rows that don't work
CREATE TABLE #ErrorRows (
RowNumber BIGINT,
KeyColumn1 INT,
KeyColumn2 INT,
[ERROR_NUMBER] INT,
[ERROR_MESSAGE] nvarchar(4000)
)
-- Begin loop to look through rows in the view
DECLARE cur INSENSITIVE CURSOR FOR
SELECT KeyColumn1, KeyColumn2
FROM SourceObject
ORDER BY KeyColumn1, KeyColumn2
OPEN cur
FETCH NEXT FROM cur
INTO @KeyColumn1, @KeyColumn2
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- Try to select the calculated columns
-- If there's an error, it will jump to the CATCH block
SELECT @CalcColumn1 = CalcColumn1,
@CalcColumn2 = CalcColumn2
FROM SourceObject
WHERE KeyColumn1 = @KeyColumn1
AND KeyColumn2 = @KeyColumn2
-- This lookup succeeded
INSERT INTO #WorkingRows
SELECT @KeyColumn1, @KeyColumn2
END TRY
BEGIN CATCH
-- The lookup failed - save details
INSERT INTO #ErrorRows
SELECT @CurrentRow,
@KeyColumn1,
@KeyColumn2,
ERROR_NUMBER(),
ERROR_MESSAGE()
END CATCH
SET @CurrentRow = @CurrentRow + 1
FETCH NEXT FROM cur
INTO @KeyColumn1, @KeyColumn2
END
-- Show the key columns of rows with errors
SELECT * FROM #ErrorRows
-- Show the key columns of working rows
SELECT * FROM #WorkingRows
-- Clean things up
close cur
deallocate cur
drop table #ErrorRows
drop table #workingrows
At the end, you’ll have two tables with results in them – #ErrorRows, which contains the key values for rows with errors in them, as well as details about the error message, and #WorkingRows, which contains the key values for all of the working rows from the view.
Note: I could just as easily set up a table with a computed column in it that causes the same problem You’d be unable to select the entire table without an error, and hunting down the row with an error is painful. The script to find the error is the same, but here’s an example of a table that has a computed column with this problem:
-- Set up table with a list of numbers in it SELECT object_id AS KeyColumn1, RANK() OVER (PARTITION BY 0 ORDER BY NEWID()) as KeyColumn2 INTO SourceObject FROM msdb.sys.columns -- Add two calculations to the table -- The first will always work -- The second will give a "Divide by zero" every 100 rows ALTER TABLE SourceObject ADD CalcColumn1 as (KeyColumn1 - KeyColumn2) ALTER TABLE SourceObject ADD CalcColumn2 as (KeyColumn1 / (KeyColumn2 % 100)) -- Note that you can't add a persisted computed column to a table -- if there's a calculation error in any of the rows, so this -- command will fail with a "Divide by zero" error ALTER TABLE SourceObject ADD CalcColumn3 as (KeyColumn1 / (KeyColumn2 % 100)) PERSISTED
