SSIS Talk

It is quite easy in SQL Server 2005 to accidentally (or intentionally, I suppose) create data corruption. It’s best to just show via a demo, so here you go:

(Ref: http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx. Thanks, Paul White, for doing all of the work for this post. )

-- In SQL Server 2005 only

-- fails
select round(0.5,0) 

-- 0.5 is cast as a numeric(1,1), which is used as the output type as well.
-- Since 0.5 rounds up to 1.0, this value is larger than a numeric(1,1) can store, 
-- which is 0.9

-- works!
select round(0.5,0) as rnd 
  into testround

-- view metadata of 'testround'  
sp_help testround  

-- Observe that the 'rnd' column is defined as a numeric(1,1), 
-- which technically means 0.9 is the max value

-- Now let's view the record:
select *
  from testround
  
-- WHAT?!  "Arithmetic Overflow"?  But, but, but the insert worked

-- Hmm, does this work? (Yes)
select cast(rnd as numeric(2,1)) as rnd
  from testround

-- Well, let's see what's going on here:

DBCC CHECKTABLE('testround') -- also catchable via DBCC CHECKDB, but not 
                             -- with PHYSICAL_ONLY

/*
DBCC results for 'testround'.
Msg 2570, Level 16, State 3, Line 1
Page (1:1811349), slot 0 in object ID 2099048, index ID 0, 
partition ID 72057594063028224, alloc unit ID 72057594096123904 (type 
"In-row data"). Column "rnd" value is out of range for data type "numeric".  
Update column to a legal value.
There are 1 rows in 2 pages for object "testround".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 
'testround' (object ID 2099048).
DBCC execution completed. If DBCC printed error messages, contact your 
system administrator.
*/

-- Okay, now let's see what's *REALLY* going on
DBCC TRACEON(3604)
GO

declare @db int
declare @filenumber int
declare @pagenumber int
set @db = db_id()
set @filenumber = 1       -- From DBCC output, adjust accordingly: Page (1:1811349)
set @pagenumber = 1811349 -- From DBCC output, adjust accordingly: Page (1:1811349)

DBCC PAGE(@db, @filenumber, @pagenumber, 3); -- 3 == full detail with ascii output 

-- Find the entry for slot 0 (according to the DBCC output above), note the out of range 
-- value: "rnd = 1.0"

/*
Slot 0 Offset 0x60 Length 12

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x67EAC060

00000000:   10000900 010a0000 000100fe ††††††††††............             

Slot 0 Column 0 Offset 0x4 Length 5

rnd = 1.0       
*/

-- Let's fix it

update testround
   set rnd = 0.9;
GO

-- Run CHECKTABLE again   
DBCC CHECKTABLE('testround') -- success!  No issues.

-- Okay, example's over.  Turn off our TRACEON command and drop the table.

-- It is important to note that this does not work in 2008.  Whew.

DBCC TRACEOFF(3604)
GO

drop table testround;
GO