February 16, 2006 at 3:52 pm
Hi,
I'm experiencing a weird situation:
I have a "basic" table, like this one:
CREATE TABLE [dbo].[Transactions] ( [IDTransaction] [int] IDENTITY (1, 1) NOT NULL , [Company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ObjectType] [int] NULL , [TransactionType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [NumKeyCol] [int] NULL , [KeyCols] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [KeyColValues] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CreatedDateTime] [datetime] NULL , [TreatedDateTime] [datetime] NULL , [Note] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Failed] [bit] NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Transactions] WITH NOCHECK ADD CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED ([IDTransaction]) ON [PRIMARY] GO
CREATE INDEX [pk_TreatedDateTime] ON [dbo].[Transactions]([TreatedDateTime]) ON [PRIMARY] GO
CREATE INDEX [PK_Failed] ON [dbo].[Transactions]([Failed]) ON [PRIMARY] GO
When I do a select using only the index, it returns 9 rows, having unique IDTransaction.
select idtransaction, failed from transactions where failed = 1
1367036, 1 1368861, 1 1386689, 1 1393734, 1 1406558, 1 1407617, 1 1409823, 1 1411004, 1 1412169, 1
When I add one field to my select Statement, like this:
select idtransaction, failed, CreatedDateTime from transactions where failed = 1
each row appears four times, with duplicate idtransactions
1367036 1 2006-02-10 10:54:28.153 1368861 1 2006-02-10 14:26:31.250 1412169 1 2006-02-16 15:51:34.723 1367036 1 2006-02-10 10:54:28.153 1368861 1 2006-02-10 14:26:31.250 1386689 1 2006-02-14 13:39:43.107 1393734 1 2006-02-15 09:51:51.363 1406558 1 2006-02-16 11:08:48.600 1407617 1 2006-02-16 13:40:09.177 1409823 1 2006-02-16 15:06:12.553 1411004 1 2006-02-16 15:32:05.190 1412169 1 2006-02-16 15:51:34.723 1386689 1 2006-02-14 13:39:43.107 1393734 1 2006-02-15 09:51:51.363 1406558 1 2006-02-16 11:08:48.600 1407617 1 2006-02-16 13:40:09.177 1409823 1 2006-02-16 15:06:12.553 1411004 1 2006-02-16 15:32:05.190 1412169 1 2006-02-16 15:51:34.723 1367036 1 2006-02-10 10:54:28.153 1368861 1 2006-02-10 14:26:31.250 1386689 1 2006-02-14 13:39:43.107 1393734 1 2006-02-15 09:51:51.363 1406558 1 2006-02-16 11:08:48.600 1407617 1 2006-02-16 13:40:09.177 1409823 1 2006-02-16 15:06:12.553 1411004 1 2006-02-16 15:32:05.190 1412169 1 2006-02-16 15:51:34.723 1367036 1 2006-02-10 10:54:28.153 1368861 1 2006-02-10 14:26:31.250 1386689 1 2006-02-14 13:39:43.107 1393734 1 2006-02-15 09:51:51.363 1406558 1 2006-02-16 11:08:48.600 1407617 1 2006-02-16 13:40:09.177 1409823 1 2006-02-16 15:06:12.553 1411004 1 2006-02-16 15:32:05.190
I have more than 1376978 rows in this table.
I suspect a paralelism problem, or pagefiles corruption... Does anybody has a clue on why this is happening?
Thanks you,
Patrick Veilleux, N'Ware Technologies
February 16, 2006 at 5:51 pm
Really weird.
Try this
select idtransaction, failed, 'Constant' from transactions where failed = 1
Amit Lohia
February 17, 2006 at 9:35 am
It returns 9 rows, no duplicates (10 actualy now since there is one more "failed" record in the table, but you know what I mean)
I will try to do dome DBCC reindex or DBCC check database this weekend to try to solve the problem...
Patrick Veilleux
February 17, 2006 at 10:00 am
Doing this query will ignore the indexes:
select idtransaction, failed, CreatedDateTime from transactions WITH (INDEX(0)) where failed = 1
and it returns the proper amount of rows.
So the table seems ok because of this query, and the index seems ok because of the "select failed from transaction where failed = 1"
It may confirm that the paralelism is in cause...
Patrick
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply