sqlserver2000 date: behavior

Showing posts with label behavior. Show all posts

Wednesday, March 21, 2012

Incorrect Results with t-sql Query in SQL Server 2005

I'm seeing some change in behavior for a query in SQL Server 2005 (compared to behavior in SQL Server 2000). The query is as follows:
create table #projects (projectid int) insert into #projects select projectid from tblprojects where istemplate = 0 and projecttemplateid = 365

Select distinct tblProjects.ProjectID
from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
select projectid
from tblMilestones
where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
and enabled = 1 )
This is dynamic SQL generated by the application when a user requests a report with variable parameters. It works fine in SQL Server 2000. It outputs 47 records which is correct.

In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).

If I modify the query even the slightest bit by:
1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
and enabled = 1 )"
To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) )
and enabled = 1 "

2) Changing " Select distinct tblProjects.ProjectID"
To " Select distinct tblProjects.ProjectID+''"

3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table

4) Adding: OPTION (FORCE ORDER)

5) OR completely fixing the query (remove redundant loops, etc)

...it works fine (outputs 47 records). It also works if I created new tables (eg. tMilestones instead of tblMilestones) and inserted about 10 records into each and ran the query referencing these new tables.

I reindexed the tables, updated stats, updated usage, ran DBCC FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave the way it does in SQL Server 2000 without modifying the query/adding the query hint.

Have you come across this? Any ideas on what might be causing the "TOP" operation. (Somewhat resembles the bug mentioned in this article: http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server 2005 yet?).

I will appreciate any new insights you might have on this issue.
Thanks much,
Smitha

P.S. Results of Showplan Text:

StmtText

SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText

Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
select tblMilestones.projectid from tblMilestones
where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006' and '4/22/2006' )
and tblMilestones.enabled = 1 )

(1 row(s) affected)

StmtText

|--Stream Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
| |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo].[tblMilestones].[Enabled],0)=(1)))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1014], [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
| | |--Merge Join(Inner Join, MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014]), RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] = [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014] = [Uniq1014]))
| | | |--Sort(ORDER BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC, [Uniq1014] ASC))
| | | | |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDate]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] >= '2006-04-15 00:00:00.000' AND [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <= '2006-04-22 00:00:00.000') ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691)) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProjects_1]), SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join)
|--Table Scan(OBJECT:([tempdb].[dbo].[#projects]), WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempdb].[dbo].[#projects].[projectid]))
|--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)

(15 row(s) affected)

StmtText
--
SET STATISTICS PROFILE OFF

(1 row(s) affected)

It would be great if you reported this at

http://lab.msdn.microsoft.com/productfeedback. If you do,

you are more likely to get the attention of the right

people, and you might find out if this is a known bug, and

if so, what the status is.

Steve Kass

Drew University

Smitha_Expesite@.discussions.microsoft.com wrote:

> I'm seeing some change in behavior for a query in SQL Server 2005

> (compared to behavior in SQL Server 2000). The query is as follows:

> create table #projects (projectid int) insert into #projects select

> projectid from tblprojects where istemplate = 0 and projecttemplateid =

> 365

> Select distinct tblProjects.ProjectID

> from tblProjects WITH (NOLOCK)

> inner join #projects on #projects.projectid = tblprojects.projectid

> Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID =

> tblMilestones.ProjectID

> and tblProjects.projectID in (

> select projectid

> from tblMilestones b

> where (parent = 683691 AND PrimaryDate between

> '4/15/2006' and '4/22/2006' )

> and enabled = 1 )

> This is dynamic SQL generated by the application when a user requests a

> report with variable parameters. It works fine in SQL Server 2000. It

> outputs 47 records which is correct.

> In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as

> a TOP operator and outputs just 1 record. (Results of Showplan Text at

> the end of this post).

> If I modify the query even the slightest bit by:

> 1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006'

> and '4/22/2006' )

> and enabled = 1 )"

> To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and

> '4/22/2006' ) )

> and enabled = 1 "

> 2) Changing " Select distinct tblProjects.ProjectID"

> To " Select distinct tblProjects.ProjectID+''"

> 3) Removing the Distinct keyword, storing into a Temp table, then

> performing a distinct on the temp table

> 4) Adding: OPTION (FORCE ORDER)

> 5) OR completely fixing the query (remove redundant loops, etc)

> ...it works fine (outputs 47 records). It also works if I created new

> tables (eg. tMilestones instead of tblMilestones) and inserted about 10

> records into each and ran the query referencing these new tables.

> I reindexed the tables, updated stats, updated usage, ran DBCC

> FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave

> the way it does in SQL Server 2000 without modifying the query/adding

> the query hint.

> Have you come across this? Any ideas on what might be causing the "TOP"

> operation. (Somewhat resembles the bug mentioned in this article:

> http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently

> fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server

> 2005 yet?).

> I will appreciate any new insights you might have on this issue.

> Thanks much,

> Smitha

> P.S. Results of Showplan Text:

> StmtText

> SET STATISTICS PROFILE ON

> (1 row(s) affected)

> StmtText

> Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)

> inner join #projects on #projects.projectid = tblprojects.projectid

> Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID =

> tblMilestones.ProjectID

> and tblProjects.projectID in (

> select tblMilestones.projectid from tblMilestones

> where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006'

> and '4/22/2006' )

> and tblMilestones.enabled = 1 )

> (1 row(s) affected)

> StmtText

> |--Stream

> Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectI

> D]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))

> |--Nested Loops(Inner Join, OUTER

> REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))

> |--Nested Loops(Inner Join, OUTER

> REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))

> |

> |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo]

> .[tblMilestones].[Enabled],0)=(1)))

> | | |--Nested Loops(Inner Join, OUTER

> REFERENCES:([Uniq1014],

> [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)

> | | |--Merge Join(Inner Join,

> MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID],

> [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID],

> [Uniq1014]),

> RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] =

> [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND

> [Uniq1014] = [Uniq1014]))

> | | | |--Sort(ORDER

> BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC,

> [Uniq1014] ASC))

> | | | | |--Index

> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDa

> te]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate]

>>= '2006-04-15 00:00:00.000' AND

> [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <=

> '2006-04-22 00:00:00.000') ORDERED FORWARD)

> | | | |--Index

> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]

> ),

> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691))

> ORDERED FORWARD)

> | | |--Clustered Index

> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid])

> ,

> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[Expesi

> teProductionCopy].[dbo].[tblMilestones].[ProjectID] AND

> [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)

> | |--Index

> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProject

> s_1]),

> SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[Expesite

> ProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)

> |--Top(TOP EXPRESSION:((1)))

> |--Nested Loops(Inner Join)

> |--Table Scan(OBJECT:([tempdb].[dbo].[#projects]),

> WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempd

> b].[dbo].[#projects].[projectid]))

> |--Clustered Index

> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid])

> ,

> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[Expesi

> teProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)

> (15 row(s) affected)

> StmtText

> --

> SET STATISTICS PROFILE OFF

> (1 row(s) affected)

>|||OK...I will report it to the Feedback folks. Thanks!|||

The Hotfix that you mentioned (http://www.kbalertz.com/Feedback_910392.aspx) is fixed in SQL Server 2005 RTM, so you may have discovered a new bug in SQL. If so, I would like to fix it. However, I do not have enough information to do that yet.

The first thing I would recommend is to send me a clone of your database. This is a version with no data, but with all the statistics intact (as though the tables were large), so we will get the same plans.

Please contact me for help making the clone.

Marc Friedman

marcfr@.removethispart.microsoft.com

|||Sorry about not getting back earlier...the issue has been fixed - not sure how or why. I couldn't get the approval to send you a clone of our database. Thanks for your input.

Incorrect Results with t-sql Query in SQL Server 2005

In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).

2) Changing " Select distinct tblProjects.ProjectID"
To " Select distinct tblProjects.ProjectID+''"

3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table

4) Adding: OPTION (FORCE ORDER)

5) OR completely fixing the query (remove redundant loops, etc)

I will appreciate any new insights you might have on this issue.
Thanks much,
Smitha

P.S. Results of Showplan Text:

StmtText

SET STATISTICS PROFILE ON

(1 row(s) affected)

(15 row(s) affected)

StmtText
--
SET STATISTICS PROFILE OFF

(1 row(s) affected)

It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is. Steve Kass Drew University Smitha_Expesite@.discussions.microsoft.com wrote:
> I'm seeing some change in behavior for a query in SQL Server 2005
> (compared to behavior in SQL Server 2000). The query is as follows:
>
> create table #projects (projectid int) insert into #projects select
> projectid from tblprojects where istemplate = 0 and projecttemplateid =
> 365
>
> Select distinct tblProjects.ProjectID
> from tblProjects WITH (NOLOCK)
> inner join #projects on #projects.projectid = tblprojects.projectid
> Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID =
> tblMilestones.ProjectID
> and tblProjects.projectID in (
> select projectid
> from tblMilestones b
> where (parent = 683691 AND PrimaryDate between
> '4/15/2006' and '4/22/2006' )
> and enabled = 1 )
>
> This is dynamic SQL generated by the application when a user requests a
> report with variable parameters. It works fine in SQL Server 2000. It
> outputs 47 records which is correct.
>
> In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as
> a TOP operator and outputs just 1 record. (Results of Showplan Text at
> the end of this post).
>
> If I modify the query even the slightest bit by:
> 1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006'
> and '4/22/2006' )
> and enabled = 1 )"
> To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and
> '4/22/2006' ) )
> and enabled = 1 "
>
> 2) Changing " Select distinct tblProjects.ProjectID"
> To " Select distinct tblProjects.ProjectID+''"
>
> 3) Removing the Distinct keyword, storing into a Temp table, then
> performing a distinct on the temp table
>
> 4) Adding: OPTION (FORCE ORDER)
>
> 5) OR completely fixing the query (remove redundant loops, etc)
>
> ...it works fine (outputs 47 records). It also works if I created new
> tables (eg. tMilestones instead of tblMilestones) and inserted about 10
> records into each and ran the query referencing these new tables.
>
> I reindexed the tables, updated stats, updated usage, ran DBCC
> FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave
> the way it does in SQL Server 2000 without modifying the query/adding
> the query hint.
>
> Have you come across this? Any ideas on what might be causing the "TOP"
> operation. (Somewhat resembles the bug mentioned in this article:
> http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently
> fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server
> 2005 yet?).
>
> I will appreciate any new insights you might have on this issue.
> Thanks much,
> Smitha
>
>
> P.S. Results of Showplan Text:
>
> StmtText
>
> SET STATISTICS PROFILE ON
>
> (1 row(s) affected)
>
> StmtText
>
>
>
>
>
>
>
> Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)
> inner join #projects on #projects.projectid = tblprojects.projectid
> Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID =
> tblMilestones.ProjectID
> and tblProjects.projectID in (
> select tblMilestones.projectid from tblMilestones
> where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006'
> and '4/22/2006' )
> and tblMilestones.enabled = 1 )
>
> (1 row(s) affected)
>
> StmtText
>
>
>
>
>
>
> |--Stream
> Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectI
> D]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
> |
> |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo]
> .[tblMilestones].[Enabled],0)=(1)))
> | | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([Uniq1014],
> [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
> | | |--Merge Join(Inner Join,
> MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID],
> [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID],
> [Uniq1014]),
> RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] =
> [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND
> [Uniq1014] = [Uniq1014]))
> | | | |--Sort(ORDER
> BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC,
> [Uniq1014] ASC))
> | | | | |--Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDa
> te]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate]
> >>= '2006-04-15 00:00:00.000' AND
>
> [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <=
> '2006-04-22 00:00:00.000') ORDERED FORWARD)
> | | | |--Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]
> ),
> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691))
> ORDERED FORWARD)
> | | |--Clustered Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid])
> ,
> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[Expesi
> teProductionCopy].[dbo].[tblMilestones].[ProjectID] AND
> [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
> | |--Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProject
> s_1]),
> SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[Expesite
> ProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
> |--Top(TOP EXPRESSION:((1)))
> |--Nested Loops(Inner Join)
> |--Table Scan(OBJECT:([tempdb].[dbo].[#projects]),
> WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempd
> b].[dbo].[#projects].[projectid]))
> |--Clustered Index
> Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid])
> ,
> SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[Expesi
> teProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
>
> (15 row(s) affected)
>
> StmtText
> --
> SET STATISTICS PROFILE OFF
>
> (1 row(s) affected)
>
>
>
>
>
>|||OK...I will report it to the Feedback folks. Thanks!|||

Please contact me for help making the clone.

Marc Friedman

marcfr@.removethispart.microsoft.com

|||Sorry about not getting back earlier...the issue has been fixed - not sure how or why. I couldn't get the approval to send you a clone of our database. Thanks for your input.sql

Incorrect Results with t-sql Query in SQL Server 2005

In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).

2) Changing " Select distinct tblProjects.ProjectID"
To " Select distinct tblProjects.ProjectID+''"

3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table

4) Adding: OPTION (FORCE ORDER)

5) OR completely fixing the query (remove redundant loops, etc)

I will appreciate any new insights you might have on this issue.
Thanks much,
Smitha

P.S. Results of Showplan Text:

StmtText

SET STATISTICS PROFILE ON

(1 row(s) affected)

(15 row(s) affected)

StmtText
--
SET STATISTICS PROFILE OFF

(1 row(s) affected)

Please contact me for help making the clone.

Marc Friedman

marcfr@.removethispart.microsoft.com

|||Sorry about not getting back earlier...the issue has been fixed - not sure how or why. I couldn't get the approval to send you a clone of our database. Thanks for your input.

Monday, March 19, 2012

Incorrect column order in syscolumns / Information_schema.columns

When dropping a column, syscolumns keeps the old ordinal
for the following columns. Is this correct / expected
behavior?
This also affects Information_schema.Columns. See example
below.
Is there any SUPPORTED way to refresh or update this info?
Regards
Hans Schyldt
Drop Table HS_Test
go
CREATE TABLE [dbo].[HS_Test] (
[C1] [int] IDENTITY (1, 1) NOT NULL ,
[C2] [int] NOT NULL ,
[C3] [varchar] (10) NOT NULL ,
[C4] [varchar] (10) NOT NULL) ON [PRIMARY]
go
select
T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
from Information_Schema.Columns as T1
where T1.Table_Name = 'HS_Test'
order by T1.Ordinal_Position
go
alter table HS_Test
Drop Column C2
go
select
T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
from Information_Schema.Columns as T1
where T1.Table_Name = 'HS_Test'
order by T1.Ordinal_Position
go
Drop Table HS_Test
goThis is correct behavior, but can be annoying.
Lot of people will argue that order and ordinal numbers do not matter, but
they matter to me.
What I did to correct this, was making a change with the Enterprise Manager
and reversing that change. Using such a change that the enterprise manager
rebuilds the whole table. In this way my ordinals where again as they were
in other databases. (So I could compare the databases again). This worked
fast enough so I didn't look for 'simpler' commands which only changed
metadata.
ben brugman
"Hans Schyldt" <hans.reply_to_forum.schyldt@.intentia.se> wrote in message
news:0e4701c367e7$70a16ec0$a101280a@.phx.gbl...
> When dropping a column, syscolumns keeps the old ordinal
> for the following columns. Is this correct / expected
> behavior?
> This also affects Information_schema.Columns. See example
> below.
> Is there any SUPPORTED way to refresh or update this info?
> Regards
> Hans Schyldt
>
> Drop Table HS_Test
> go
> CREATE TABLE [dbo].[HS_Test] (
> [C1] [int] IDENTITY (1, 1) NOT NULL ,
> [C2] [int] NOT NULL ,
> [C3] [varchar] (10) NOT NULL ,
> [C4] [varchar] (10) NOT NULL) ON [PRIMARY]
> go
> select
> T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
> from Information_Schema.Columns as T1
> where T1.Table_Name = 'HS_Test'
> order by T1.Ordinal_Position
> go
> alter table HS_Test
> Drop Column C2
> go
> select
> T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
> from Information_Schema.Columns as T1
> where T1.Table_Name = 'HS_Test'
> order by T1.Ordinal_Position
> go
> Drop Table HS_Test
> go

Incorrect behavior in 'NOT IN' subquery with OPENXML

In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a subquery.
However, this does not always return the desired result.
I've tried to simplify this problem and I've ended up with the sql script
below. In short, it creates a table with 200 records, select all records
except two specified in an xml document. When I run the complete script (in
the Query Analyzer) I get the desired results: 198 records. But when I first
run the part of the script where the table is created and filled (up to the
line of dashes), end then run the rest of the script where the xml is
prepared and the query is executed I get another result: 200 records.
I tried this script on two different Sql Server 2000 SP4 machines, both have
the same behavior. But this behavior seems incorrect to me.
So my questions:
- Does anybody get the correct result when you execute this script in parts?
- I this behavior indeed indeed incorrect or am I not thinking straight?
- Or could this be a bug in SQL Server...?
Thanks for your help!
Some remarks:
- When the table contains 160 records or less I get the correct result.
- When I use an 'IN' subquery (so without the NOT) I always get the correct
result (so the two specified records are returned both in the 'IN' as in the
'NOT IN' subquery...).
- When I run the subquery separately it returns the expected result: two
rows with values 1 and 2.
SET NOCOUNT ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Test]
CREATE TABLE [dbo].[Test] (
TestId int NOT NULL PRIMARY KEY
)
DECLARE @.Value int
SET @.Value = 1
WHILE @.Value <= 200
BEGIN
INSERT INTO Test (TestId) VALUES (@.Value)
SET @.Value = @.Value + 1
END

DECLARE @.Xml nvarchar(4000)
DECLARE @.XmlHandle int
SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
SELECT TestId
FROM Test
WHERE TestId NOT IN (
SELECT TestId
FROM OPENXML(@.XmlHandle, '/Root/Row')
WITH (
TestIdint'ID'
)
)
ORDER BY TestId
EXEC sp_xml_removedocument @.XmlHandle
DROP TABLE [dbo].[Test]
SET NOCOUNT OFF
I have tried both in SQL Server 2005 and got the same result (198 rows). I
don't have an SP4 installation on my machine, but asked our test team to
investigate. It may take a couple of days though until we can get back to
you...
Best regards
Michael
"Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
> In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a
> subquery.
> However, this does not always return the desired result.
> I've tried to simplify this problem and I've ended up with the sql script
> below. In short, it creates a table with 200 records, select all records
> except two specified in an xml document. When I run the complete script
> (in
> the Query Analyzer) I get the desired results: 198 records. But when I
> first
> run the part of the script where the table is created and filled (up to
> the
> line of dashes), end then run the rest of the script where the xml is
> prepared and the query is executed I get another result: 200 records.
> I tried this script on two different Sql Server 2000 SP4 machines, both
> have
> the same behavior. But this behavior seems incorrect to me.
> So my questions:
> - Does anybody get the correct result when you execute this script in
> parts?
> - I this behavior indeed indeed incorrect or am I not thinking straight?
> - Or could this be a bug in SQL Server...?
> Thanks for your help!
> Some remarks:
> - When the table contains 160 records or less I get the correct result.
> - When I use an 'IN' subquery (so without the NOT) I always get the
> correct
> result (so the two specified records are returned both in the 'IN' as in
> the
> 'NOT IN' subquery...).
> - When I run the subquery separately it returns the expected result: two
> rows with values 1 and 2.
>
> SET NOCOUNT ON
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
> DROP TABLE [dbo].[Test]
> CREATE TABLE [dbo].[Test] (
> TestId int NOT NULL PRIMARY KEY
> )
> DECLARE @.Value int
> SET @.Value = 1
> WHILE @.Value <= 200
> BEGIN
> INSERT INTO Test (TestId) VALUES (@.Value)
> SET @.Value = @.Value + 1
> END
>
> ----
>
> DECLARE @.Xml nvarchar(4000)
> DECLARE @.XmlHandle int
> SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
> EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
> SELECT TestId
> FROM Test
> WHERE TestId NOT IN (
> SELECT TestId
> FROM OPENXML(@.XmlHandle, '/Root/Row')
> WITH (
> TestId int 'ID'
> )
> )
> ORDER BY TestId
> EXEC sp_xml_removedocument @.XmlHandle
> DROP TABLE [dbo].[Test]
> SET NOCOUNT OFF
>
>
|||We checked it against SQL 2000 SP3a and SP4. First the good news: SP4 seems
to work fine in either case.
When running it under SP3a: we observed:
1) run whole script - gets 198 rows (only one Remote Scan involved)
2) run create/fill 'Test' table first, and then OpenXML part, get 200 rows
(wrong behavior, two Remote Scans involved).
We seem to be able to correct this by setting "set ansi_nulls off".
Can you please check with select @.@.version what version number you are
running? And if you see version 8.00.2039 can you please tell us how you
installed it?
Thanks
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u6dGBzyZFHA.3568@.TK2MSFTNGP10.phx.gbl...
>I have tried both in SQL Server 2005 and got the same result (198 rows). I
>don't have an SP4 installation on my machine, but asked our test team to
>investigate. It may take a couple of days though until we can get back to
>you...
> Best regards
> Michael
> "Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
> news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
>
|||Before posting I checked @.@.VERSION and since it mentioned SP4 I figured I had
SQL Server 2000 SP4. Only now did I read it a bit better to see we're running
2000 SP3:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
(Sorry, I got fooled by the "Service Pack 4" at the end... :-$ )
The "set ansi_nulls off" indeed seems to correct this issue.
I did find another way of circumventing this behavior: inserting the values
from the xml into a temporary table and using that temporary table in the
subquery.
In any case: I understand that this behavior is corrected in the next SP, so
that's good. And of course thank you for your time and help with this matter.
Regards,
Wouter

Incorrect behavior in 'NOT IN' subquery with OPENXML

In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a subquery.
However, this does not always return the desired result.
I've tried to simplify this problem and I've ended up with the sql script
below. In short, it creates a table with 200 records, select all records
except two specified in an xml document. When I run the complete script (in
the Query Analyzer) I get the desired results: 198 records. But when I first
run the part of the script where the table is created and filled (up to the
line of dashes), end then run the rest of the script where the xml is
prepared and the query is executed I get another result: 200 records.
I tried this script on two different Sql Server 2000 SP4 machines, both have
the same behavior. But this behavior seems incorrect to me.
So my questions:
- Does anybody get the correct result when you execute this script in parts?
- I this behavior indeed indeed incorrect or am I not thinking straight?
- Or could this be a bug in SQL Server...?
Thanks for your help!
Some remarks:
- When the table contains 160 records or less I get the correct result.
- When I use an 'IN' subquery (so without the NOT) I always get the correct
result (so the two specified records are returned both in the 'IN' as in the
'NOT IN' subquery...).
- When I run the subquery separately it returns the expected result: two
rows with values 1 and 2.
SET NOCOUNT ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Test]
CREATE TABLE [dbo].[Test] (
TestId int NOT NULL PRIMARY KEY
)
DECLARE @.Value int
SET @.Value = 1
WHILE @.Value <= 200
BEGIN
INSERT INTO Test (TestId) VALUES (@.Value)
SET @.Value = @.Value + 1
END
----
DECLARE @.Xml nvarchar(4000)
DECLARE @.XmlHandle int
SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
SELECT TestId
FROM Test
WHERE TestId NOT IN (
SELECT TestId
FROM OPENXML(@.XmlHandle, '/Root/Row')
WITH (
TestId int 'ID'
)
)
ORDER BY TestId
EXEC sp_xml_removedocument @.XmlHandle
DROP TABLE [dbo].[Test]
SET NOCOUNT OFFI have tried both in SQL Server 2005 and got the same result (198 rows). I
don't have an SP4 installation on my machine, but asked our test team to
investigate. It may take a couple of days though until we can get back to
you...
Best regards
Michael
"Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
> In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a
> subquery.
> However, this does not always return the desired result.
> I've tried to simplify this problem and I've ended up with the sql script
> below. In short, it creates a table with 200 records, select all records
> except two specified in an xml document. When I run the complete script
> (in
> the Query Analyzer) I get the desired results: 198 records. But when I
> first
> run the part of the script where the table is created and filled (up to
> the
> line of dashes), end then run the rest of the script where the xml is
> prepared and the query is executed I get another result: 200 records.
> I tried this script on two different Sql Server 2000 SP4 machines, both
> have
> the same behavior. But this behavior seems incorrect to me.
> So my questions:
> - Does anybody get the correct result when you execute this script in
> parts?
> - I this behavior indeed indeed incorrect or am I not thinking straight?
> - Or could this be a bug in SQL Server...?
> Thanks for your help!
> Some remarks:
> - When the table contains 160 records or less I get the correct result.
> - When I use an 'IN' subquery (so without the NOT) I always get the
> correct
> result (so the two specified records are returned both in the 'IN' as in
> the
> 'NOT IN' subquery...).
> - When I run the subquery separately it returns the expected result: two
> rows with values 1 and 2.
>
> SET NOCOUNT ON
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
> DROP TABLE [dbo].[Test]
> CREATE TABLE [dbo].[Test] (
> TestId int NOT NULL PRIMARY KEY
> )
> DECLARE @.Value int
> SET @.Value = 1
> WHILE @.Value <= 200
> BEGIN
> INSERT INTO Test (TestId) VALUES (@.Value)
> SET @.Value = @.Value + 1
> END
>
> ----
>
> DECLARE @.Xml nvarchar(4000)
> DECLARE @.XmlHandle int
> SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
> EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
> SELECT TestId
> FROM Test
> WHERE TestId NOT IN (
> SELECT TestId
> FROM OPENXML(@.XmlHandle, '/Root/Row')
> WITH (
> TestId int 'ID'
> )
> )
> ORDER BY TestId
> EXEC sp_xml_removedocument @.XmlHandle
> DROP TABLE [dbo].[Test]
> SET NOCOUNT OFF
>
>|||We checked it against SQL 2000 SP3a and SP4. First the good news: SP4 seems
to work fine in either case.
When running it under SP3a: we observed:
1) run whole script - gets 198 rows (only one Remote Scan involved)
2) run create/fill 'Test' table first, and then OpenXML part, get 200 rows
(wrong behavior, two Remote Scans involved).
We seem to be able to correct this by setting "set ansi_nulls off".
Can you please check with select @.@.version what version number you are
running? And if you see version 8.00.2039 can you please tell us how you
installed it?
Thanks
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u6dGBzyZFHA.3568@.TK2MSFTNGP10.phx.gbl...
>I have tried both in SQL Server 2005 and got the same result (198 rows). I
>don't have an SP4 installation on my machine, but asked our test team to
>investigate. It may take a couple of days though until we can get back to
>you...
> Best regards
> Michael
> "Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
> news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
>|||Before posting I checked @.@.VERSION and since it mentioned SP4 I figured I ha
d
SQL Server 2000 SP4. Only now did I read it a bit better to see we're runnin
g
2000 SP3:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
(Sorry, I got fooled by the "Service Pack 4" at the end... :-$ )
The "set ansi_nulls off" indeed seems to correct this issue.
I did find another way of circumventing this behavior: inserting the values
from the xml into a temporary table and using that temporary table in the
subquery.
In any case: I understand that this behavior is corrected in the next SP, so
that's good. And of course thank you for your time and help with this matter
.
Regards,
Wouter

Friday, February 24, 2012

In(..) and Like xxxx% performance

Hi,
I am experiencing strange SQL server behavior,

The table has an index on (filed1, field2) - which is primary key.
if I do select where field1 = 'a' and field2 like '123%' runs fast.
if I do select where field1 = 'b' and field2 like '123%' runs fast
too.

if I do select where field1 in ('a','b') and field2 like '123%' the
damn thing runs forever.

What is interesting, the SQL plan is perfectly using primary index and
selection is translated into "good" ranges for field2. It fills like
server does not take in consideration that field2 is in index.

Any ideas what's going on? I'd hate to use two selects to avoid
problem.

Thank you.
M.Q.Mark Quest (nospamhere2@.yahoo.com) writes:
> I am experiencing strange SQL server behavior,
> The table has an index on (filed1, field2) - which is primary key.
> if I do select where field1 = 'a' and field2 like '123%' runs fast.
> if I do select where field1 = 'b' and field2 like '123%' runs fast
> too.
> if I do select where field1 in ('a','b') and field2 like '123%' the
> damn thing runs forever.
> What is interesting, the SQL plan is perfectly using primary index and
> selection is translated into "good" ranges for field2. It fills like
> server does not take in consideration that field2 is in index.
> Any ideas what's going on? I'd hate to use two selects to avoid
> problem.

Sounds a bit funny, but without knowledge of the table and query plan,
it's a little bit difficult to say what is going on.

If you can demonstrate the situation with a script that anyone could
copy and paste and run on his machine, that would be something to bring
up with Microsoft. But I would guess that it is not that simple to
reproduce.

But maybe you could post the CREATE TABLE and CREATE INDEX statements
for the table, and also give some indication of sizes and data distribution?
And the exact text of the slow query as well as the output from
SET SHOWPLAN_ALL ON for the query.

A faint possibility is that there is blocking or corruption. Check the
latter with DBCC CHECKDB.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 21, 2012

Incorrect Results with t-sql Query in SQL Server 2005

Incorrect Results with t-sql Query in SQL Server 2005

Incorrect Results with t-sql Query in SQL Server 2005

Monday, March 19, 2012

Incorrect column order in syscolumns / Information_schema.columns

Incorrect behavior in 'NOT IN' subquery with OPENXML

Incorrect behavior in 'NOT IN' subquery with OPENXML

Friday, February 24, 2012

In(..) and Like xxxx% performance

sqlserver2000 date

Blog Archive

About Me