Sql-server – Error when creating an index in SQL Server 2012

indexsql-server-2012

I'm trying to create the following index on our test environment (SQL Server 2012):

CREATE NONCLUSTERED INDEX [iTransaction-OriginatingTransaction] ON [dbo].[Transaction]
(
    [OriginatingTransactionID_EOT] ASC,
    [OriginatingTransactionID] ASC
)

on our transaction table:

CREATE TABLE [dbo].[Transaction](
    [TransactionID] [int] IDENTITY(1,1) NOT NULL,
    [OriginatingTransactionID_EOT] [varchar](50) NULL,
    [OriginatingTransactionID] [int] NULL,
    [Status] [int] NOT NULL,
    [BusinessID] [int] NOT NULL,
    [CreatedUserID] [int] NOT NULL,
    [CreatedDateTime] [datetime] NOT NULL,
    [ModifiedUserID] [int] NOT NULL,
    [ModifiedDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED 
(
    [TransactionID] ASC
)

I get the following error:

Msg 681, Level 16, State 3, Line 2
Attempting to set a non-NULL-able column's value to NULL.

If I run the same create index statement on our live database (SQL Server 2005) then it works without any errors.

Why can't I create this index on SQL Server 2012?

UPDATE: Results of dbcc checktable:

Msg 8944, Level 16, State 13, Line 1
Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data), page (1:701352), row 13. Test (ColumnOffsets <= (nextRec – pRec)) failed. Values are 7198 and 186.
Msg 8944, Level 16, State 13, Line 1
Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data), page (1:701352), row 13. Test (ColumnOffsets <= (nextRec – pRec)) failed. Values are 7198 and 186.
Msg 8928, Level 16, State 1, Line 1
Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data): Page (1:701352) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data). Page (1:701352) was not seen in the scan although its parent (1:709848) and previous (1:700903) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 954995248, index ID 1, partition ID 72057594796965888, alloc unit ID 72057595543093248 (type In-row data). Page (1:701353) is missing a reference from previous page (1:701352). Possible chain linkage problem.
DBCC results for 'Transaction'.
There are 2354636 rows in 57941 pages for object "Transaction".
CHECKTABLE found 0 allocation errors and 5 consistency errors in table 'Transaction' (object ID 954995248).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (Redebiz.dbo.Transaction).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Best Answer

The problem was with data corruption in the table. Once the issues found in dbcc checktable were resolved (in this case we deleted the affected rows as it's only test data) the index was able to be created.