same WHERE affects different rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

same WHERE affects different rows

Author

Topic

chiri
Starting Member

12 Posts

Posted - 2006-07-04 : 18:30:27

Hi to all, my problem is this:

1. I open query analizer and connect to a DB server on another IP in the same network.

2. I execute the following statements on a table that has a clustered index (descending) on the column DATETIMEFIELD that appears in the where clauses.

select * from MYTABLE
where DATETIMEFIELD < dateadd(day,-29,getdate())

delete MYTABLE
where DATETIMEFIELD < dateadd(day,-29,getdate())

3. The funny thing is, the above commands do not affect the same rows!

4. If I take away the index, then both commands affect the same rows.

5. The same works fine on another server (with the index).

Thanks for any comments.
Cristian

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-04 : 19:04:35

The DATETIMEFIELD contains date & time ? or rather the DATETIMEFIELD contains time > 00:00 ?

select dateadd(day, -29, getdate()) will returns you different date & time everytime you run even if it is run 5 mins after the other. Could it be that there are some records that falls between these time lapse ?

chiri
Starting Member

12 Posts

Posted - 2006-07-04 : 19:10:41

Hi khtan,

thanx for the reply. I need to be more specific:

1. The DATETIME column is of type datetime.
2. When I run the delete clause on the indexed table, it affects the rows in which DATETIME > dateadd(day,-29,getdate())!!!

Hope that's more clear.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-04 : 19:14:54

try using this

DATETIMEFIELD > dateadd(day, datediff(day, 0, getdate()), -29)

instead of DATETIMEFIELD > dateadd(day,-29,getdate())!!!

chiri
Starting Member

12 Posts

Posted - 2006-07-05 : 00:07:49

Hi khtan,

thanx again. Ok, I tried what you propose but got the same result. I want to emphasize that the weird thing is that I ask for:

DATETIME < something

and the rows affected are:

DATETIME > something (maybe >=, I'm not sure)

but only for the DELETE statement, not the SELECT statement.

I can force it to work by converting the dates to 'YYYYMMDD' format but I wish to know why it does what it does in the first place.

Any clues????

Cheers.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-05 : 01:42:45

Could you give an example of the DATETIME of the rows that gets deleted but not selected?

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

chiri
Starting Member

12 Posts

Posted - 2006-07-05 : 09:21:00

Of course mate, here goes:

The table may have the folowing data:

DATETIME VALUE1 VALUE2
--------------------------------------------
2006-06-01 12:27:00.152 120 500
2006-06-01 12:26:01.133 130 670
2006-06-01 12:25:00.111 160 780
2006-06-01 12:24:00.099 170 990
2006-06-01 12:23:01.001 110 1070
2006-06-01 12:22:00.987 180 1760
2006-06-01 12:21:01.038 110 3450
...(etc)
2006-05-03 12:27:01.038 110 450
2006-05-03 12:26:00.268 110 650
2006-05-03 12:25:00.256 110 890
------------------------------------------

If the current datetime is 2006-06-01 12:28:00.000 (that is, when I execute the commands). Then the where clause should choose all rows prior to 2006-05-03 12:28:00.000, in this case, the last 3 rows in the table above. The thing is, the SELECT statement DOES choose those rows, but the DELETE statement chooses all rows ABOVE it (remember that the table has a clustered descending index on the DATETIME field).

Hope that helps to clear things up a bit...

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-05 : 09:28:52

Has the two servers the same version of SQL Server, and which?

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-05 : 09:46:48

quote:
Originally posted by chiri

The thing is, the SELECT statement DOES choose those rows, but the DELETE statement chooses all rows ABOVE it (remember that the table has a clustered descending index on the DATETIME field).

Are you really, really sure that there is a < in both statements?

select * from MYTABLE
where DATETIMEFIELD < dateadd(day,-29,getdate())

delete MYTABLE
where DATETIMEFIELD < dateadd(day,-29,getdate())

Peter Larsson
Helsingborg, Sweden

chiri
Starting Member

12 Posts

Posted - 2006-07-05 : 10:42:21

PSamsig: yes both servers have SQL Server 2000 Enterprise Edition
Peso: I am absolutely sure.

Cheers.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-05 : 10:53:14

I meant version including service packs

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

chiri
Starting Member

12 Posts

Posted - 2006-07-05 : 11:22:16

SERVER 1 (local)

@@VERSION =
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

xp_msver:

1 ProductName NULL Microsoft SQL Server
2 ProductVersion 524288 8.00.194
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2000.080.0194.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © 1988-2000 Microsoft Corp. All
rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark
of Microsoft Corporation. Windows(TM)
is a trademark of Microsoft
Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 65630 NULL
15 WindowsVersion 143851525 5.0 (2195)
16 ProcessorCount 4 4
17 ProcessorActiveMask 15 0000000f
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 3967 3967 (4159680512)
20 Product ID NULL NULL
---------------------------------------------------------------------

SERVER 2 (the one I connect to)

@@VERSION =
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

xp_msver:
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 524288 8.00.194
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2000.080.0194.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © 1988-2000 Microsoft Corp. All
rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark
of Microsoft Corporation. Windows(TM)
is a trademark of Microsoft
Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 65630 NULL
15 WindowsVersion 143851525 5.0 (2195)
16 ProcessorCount 4 4
17 ProcessorActiveMask 15 0000000f
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 2047 2047 (2146394112)
20 Product ID NULL NULL

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-05 : 11:50:15

Hmm, seems only difference is the amount of RAM, I would without doubt update with a service pack. The list of things changed since the initial release is HUGE. Im not even sure it is worth (or possible) figuring out what the real cause is until you do so.

Read this for instance: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36672

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

chiri
Starting Member

12 Posts

Posted - 2006-07-05 : 12:00:45

That sounds totally right to me. The server isn't mine, but as a client I'll ask for it. Thanx, I'll let you all know how this goes...

chiri
Starting Member

12 Posts

Posted - 2006-07-06 : 00:20:58

OK guys, the diagnosis and cure have been found, thanx to your help.

SQL Server 2000 RTM, that is, with no service pack, has the following bug:

[url]http://support.microsoft.com/kb/293484/[/url]

SP1 fixes this. I prooved the bug and fix on my PC, starting with a canonical version of SQL Server and applying the service pack.

Thanx again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources