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