Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
The reason for the different database versions is that we're in the middle of upgrading to 2012 –  Greg Oct 15 '13 at 2:41
3  
Run a DBCC CHECKDB (technet.microsoft.com/en-us/library/ms176064.aspx) and report back if any errors were found –  Sebastian Meine Oct 15 '13 at 2:45
3  
...or start with just DBCC CHECKTABLE –  Aaron Bertrand Oct 15 '13 at 3:06
    
Also, is it possible you have a DDL trigger enabled in the database? Line 2 seems funny to me for that exact statement, and I'm wondering if the error is actually occurring elsewhere. –  Aaron Bertrand Oct 15 '13 at 3:07
    
You should be able to create the index. I was able to recreate your table, with some test data, and add the index without issue. As Aaron said, "Line 2" is a key point, since there is no line 2 in the CREATE INDEX statement. –  Max Vernon Oct 15 '13 at 3:22

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.