moon44
asked on
Different results depending on clustered or unclustered index
Hello,
Short story: I get different results in SQL 2008 depending on if a index is clustered or not. As well as the amount of rows (size) in that table. Same data on SQL Server 2005 works fine. The database is set to be compatible with version 80 (2000), changing that does not effect the result.
The problem is, the where clause is not processed as a filter.
Long (reproduce):
Please execute the following SQL-Statements on a 2008 Server. They create two tables and fill them with some rows.
After that, please execute the following select statement
declare @myID int;
set @myID=15;
SELECT * FROM tLeft LEFT OUTER JOIN tRight
ON tLeft.GroupID = tRight.GroupID
WHERE tLeft.bView=1 AND (tRight.ForeignKeyID=@myID
You will get 3 result-rows. But UserID and ForeignKeyID are NOT 15 as I define it in the where clause!
If you change the index "IX_tRight_GroupID" to clustered (or reduce the rows in that table massively) it works as expected (no results are returned).
If you execute that on a SQL 2005 everything works as expected from the beginning.
I added an image of the execution plans.
How can that be? Is this a bug or a feature? Is there any setting to set
Short story: I get different results in SQL 2008 depending on if a index is clustered or not. As well as the amount of rows (size) in that table. Same data on SQL Server 2005 works fine. The database is set to be compatible with version 80 (2000), changing that does not effect the result.
The problem is, the where clause is not processed as a filter.
Long (reproduce):
Please execute the following SQL-Statements on a 2008 Server. They create two tables and fill them with some rows.
After that, please execute the following select statement
declare @myID int;
set @myID=15;
SELECT * FROM tLeft LEFT OUTER JOIN tRight
ON tLeft.GroupID = tRight.GroupID
WHERE tLeft.bView=1 AND (tRight.ForeignKeyID=@myID
You will get 3 result-rows. But UserID and ForeignKeyID are NOT 15 as I define it in the where clause!
If you change the index "IX_tRight_GroupID" to clustered (or reduce the rows in that table massively) it works as expected (no results are returned).
If you execute that on a SQL 2005 everything works as expected from the beginning.
I added an image of the execution plans.
How can that be? Is this a bug or a feature? Is there any setting to set
/****** Object: Table [dbo].[tLeft] Script Date: 11/18/2008 07:09:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tLeft]') AND type in (N'U'))
DROP TABLE [dbo].[tLeft]
GO
CREATE TABLE [dbo].[tLeft](
[FileID] [int] NOT NULL,
[UserID] [int] NULL,
[GroupID] [int] NULL,
[isTempACE] [bit] NOT NULL,
[bView] [bit] NOT NULL,
[sRights] [varchar](100) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_tLeft] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tLeft] ADD CONSTRAINT [DF_tLeft_isTempACE] DEFAULT ((0)) FOR [isTempACE]
GO
ALTER TABLE [dbo].[tLeft] ADD CONSTRAINT [DF_tLeft_bView] DEFAULT ((0)) FOR [bView]
GO
/****** Object: Index [IX_roxFileACE] Script Date: 11/18/2008 07:21:54 ******/
CREATE NONCLUSTERED INDEX [IX_tLeft_FileID] ON [dbo].[tLeft]
(
[FileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
insert into tLeft (fileid, userid, groupid, istempace,bview, srights) values(589,68,NULL,0,1,'R1R3V1V2V6E1E3E4X1X2X4X5X6W1W2W3W9')
insert into tLeft (fileid, userid, groupid, istempace,bview, srights) values(564,68,NULL,0,1,'R1R3V1V2V6E1E3E4X1X2X4X5X6W1W2W3W9')
insert into tLeft (fileid, userid, groupid, istempace,bview, srights) values(594,68,NULL,0,1,'R1R3V1V2V6E1E3E4X1X2X4X5X6W1W2W3W9')
/****** Object: Table [dbo].[tRight] Script Date: 11/18/2008 07:10:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tRight]') AND type in (N'U'))
DROP TABLE [dbo].[tRight]
GO
CREATE TABLE [dbo].[tRight](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GroupID] [int] NULL,
[ForeignKeyID] [int] NULL,
[SubgroupID] [int] NULL,
[Type] [varchar](10) NULL,
[isAutomatic] [bit] NOT NULL,
CONSTRAINT [PK_tRight] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tRight] ADD CONSTRAINT [DF_tRight_SubgroupID] DEFAULT (NULL) FOR [SubgroupID]
GO
ALTER TABLE [dbo].[tRight] ADD CONSTRAINT [DF_tRight_isAutomatic] DEFAULT ((0)) FOR [isAutomatic]
GO
/****** Object: Index [IX_roxGroupMembers_GroupID] Script Date: 11/18/2008 07:19:31 ******/
CREATE NONCLUSTERED INDEX [IX_tRight_GroupID] ON [dbo].[tRight]
(
[GroupID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
declare @i int;
set @i=0;
while @i<500
begin
insert into tRight (GroupID, ForeignKeyID,SubgroupID,Type,isAutomatic) VALUES (1, NULL, 2, 'Con', 0)
set @i=@i+1;
end
Scott Pletcher
Not sure what specific values you are getting in the result set -- that would have been nice to see -- but you are doing a LEFT OUTER JOIN, so you should get a back *every row* "WHERE tLeft.bView=1", with the tRight columns NULL if a match was not found.
ASKER
I attach a screenshot of the results.
As you can see, the where clause is not processes correctly. I 2005 it works. As I wrote.
sql2.png
As you can see, the where clause is not processes correctly. I 2005 it works. As I wrote.
sql2.png
That *is* a valid result. You did a LEFT OUTER JOIN, not an INNER JOIN. SQL does not need to find a match on the tRight table to include the row. I think the wrong result is from 2005, not 2008.
Agree with scott.
the left outer join means "i don't care about tright having matching entries"
so long as tleft = 1, then the second qualifier is failing because of NULL and you can choose to ignore NULL conditions in some settings...
try this to test the NULL, or non-join of tright:
declare @myID int;
set @myID=15;
SELECT *
FROM tLeft
LEFT OUTER JOIN tRight ON tLeft.GroupID = tRight.GroupID
WHERE tLeft.bView=1 AND (isnull(tRight.ForeignKeyI
the left outer join means "i don't care about tright having matching entries"
so long as tleft = 1, then the second qualifier is failing because of NULL and you can choose to ignore NULL conditions in some settings...
try this to test the NULL, or non-join of tright:
declare @myID int;
set @myID=15;
SELECT *
FROM tLeft
LEFT OUTER JOIN tRight ON tLeft.GroupID = tRight.GroupID
WHERE tLeft.bView=1 AND (isnull(tRight.ForeignKeyI
Bit strange that it only manifests with non-clustered I do agree wholeheartedly with you on that score...
One thought...
SET ANSI_DEFAULTS can control SET ANSI_NULL and there are 7 such settings that can affect indexes (and more)... ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL must also be set to ON, and NUMERIC_ROUNDABORT must be set to OFF when creating indexes.
have a look in BOL : null values [SQL Server] and the subcategory "about..." has a decision table in there - kind of describes what is happening to you. But again, would not have thought there was a difference from a clustered index and a non-clustered index unless some of the ansi settings have impacted somewhere along the line - if not, then I would say it is a bug, and then would also go on to say, that you really must manage NULL yourself. I did have a SQL2008 NULL behaviour where in batch mode it will always fail despite some of the settings (it was an EE question and was "fixed") - seemed at the time that 2008 does in fact handle NULL in a lot more stringent fashion.
SET ANSI_DEFAULTS can control SET ANSI_NULL and there are 7 such settings that can affect indexes (and more)... ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL must also be set to ON, and NUMERIC_ROUNDABORT must be set to OFF when creating indexes.
have a look in BOL : null values [SQL Server] and the subcategory "about..." has a decision table in there - kind of describes what is happening to you. But again, would not have thought there was a difference from a clustered index and a non-clustered index unless some of the ansi settings have impacted somewhere along the line - if not, then I would say it is a bug, and then would also go on to say, that you really must manage NULL yourself. I did have a SQL2008 NULL behaviour where in batch mode it will always fail despite some of the settings (it was an EE question and was "fixed") - seemed at the time that 2008 does in fact handle NULL in a lot more stringent fashion.
ASKER
I know what a left join does. And it is what I want to use.
But why is the where-clause not used as a filter?
The tLeft.userid does not match with the parameter value.
If I leave one of the ORs away the result is correct (no rows).
Doing so it does not matter if I leave the
tRight.ForeignKeyID=@myID
or the
tLeft.UserID = @myID
out of the statement. If it is anything about the NULLs it should not matter if there is a
"tLeft.UserID = @myID" in the statement or not, correct?
And why does it matter if there are more or less than 235 (equal) rows in the tRight table? This should not get into the result either.
I think I have to open a support case at microsoft for that.
But why is the where-clause not used as a filter?
The tLeft.userid does not match with the parameter value.
If I leave one of the ORs away the result is correct (no rows).
Doing so it does not matter if I leave the
tRight.ForeignKeyID=@myID
or the
tLeft.UserID = @myID
out of the statement. If it is anything about the NULLs it should not matter if there is a
"tLeft.UserID = @myID" in the statement or not, correct?
And why does it matter if there are more or less than 235 (equal) rows in the tRight table? This should not get into the result either.
I think I have to open a support case at microsoft for that.
Did you try the isnull bit in the where clause ?
when you are doing an AND on a condition, the entire condition is treated as a whole - ie a single result then within the bracket, we have the OR clause , but if there is NULL, then it can change the behaviour of the condition. 2008 does evaluate a bit differently that earlier (you say 2000 compatability mode), so, it can produce a subtley different result depending on how and when it actuall evaluates filter operations, and determines the likely outcomes before retrieving any data at all. Meaning, it may well have determined that an "UNKNOWN" condition is possible from the left join and used in the filter conditions of the where clause, and as soon as an "unknown" condition partially exists, then the entire condition set (ie within the brackets) is also likely to yield an unknown condition.
Some of the above is in fact described reasonably well in Books On Line :
Also, 2008 has this to say :
Sometimes, SQL Server 2008 evaluates expressions in queries sooner than SQL Server 2000. This behavior provides the following important benefits:
Indexes on computed columns can be matched to expressions in a query that are the same as the computed column expression.
Redundant computation of the expression results is prevented.
However, depending on the nature of the query and the data in the database, run-time exceptions could occur in SQL Server 2008 if the query has an existing unsafe expression. These run-time exceptions include the following:
Arithmetic exceptions: zero-divide, overflow, and underflow.
Conversion failures such as loss of precision and an attempt to convert a nonnumeric string to a number.
Aggregation over a set of values that are not all guaranteed to be nonnull.
In SQL Server 2000, these exceptions may not occur in a particular application that uses specific data. However, a query plan that is changed because of changing statistics potentially could lead to an exception in SQL Server 2008. You can prevent these run-time exceptions by modifying the query to include conditional expressions such as NULLIF or CASE.
Important:
Expressions that appear in a search condition, select list, or any other location within a query may be broken down and reorganized into one or more independent expressions. SQL Server may evaluate these independent expressions in any order with respect to each other. Filtering operations, including joins, are not necessarily applied before the result columns are computed.
I would suggest that with the introduction of a clustered index, and the size of the table, does in fact change the query plan, and whilst not an exception per se, has created an event where by the "reasonably expect" behaviour is a bit different. We all know that SQL 2008 has been updated in some of these areas, and possibly never really manifested itself until now.
Also, have a look at that reference : http://msdn.microsoft.com/en-us/library/ms191504.aspx
look at the tables between two boolean operands (one is horizontal, the other is vertical and the grid is the intersection of those outcomes).
Looking at the "OR" condition we had operand 1 = unknown and operand 2 = false -- net result = UNKNOWN
Now, looking at the AND condition, we have operand 1 = true and operand 2 (ie the above) = unknown -- net result = unknown, but I think being bracketed it is evaluating the first operand to be true.
Bottom line for me is it simply re-inforces the need to explicitly manage NULLs, and just another example (assuming that the query with isnull worked) and that's what the book says as well, and the reason being is to avoid unpredictable results such as you are experiencing.
However, would be worth raising a support case - mainly because the behaviour should be the same in a clustered or non clustered index, apart from the disclaimer that BOL says above (about changing query plans).
when you are doing an AND on a condition, the entire condition is treated as a whole - ie a single result then within the bracket, we have the OR clause , but if there is NULL, then it can change the behaviour of the condition. 2008 does evaluate a bit differently that earlier (you say 2000 compatability mode), so, it can produce a subtley different result depending on how and when it actuall evaluates filter operations, and determines the likely outcomes before retrieving any data at all. Meaning, it may well have determined that an "UNKNOWN" condition is possible from the left join and used in the filter conditions of the where clause, and as soon as an "unknown" condition partially exists, then the entire condition set (ie within the brackets) is also likely to yield an unknown condition.
Some of the above is in fact described reasonably well in Books On Line :
Also, 2008 has this to say :
Sometimes, SQL Server 2008 evaluates expressions in queries sooner than SQL Server 2000. This behavior provides the following important benefits:
Indexes on computed columns can be matched to expressions in a query that are the same as the computed column expression.
Redundant computation of the expression results is prevented.
However, depending on the nature of the query and the data in the database, run-time exceptions could occur in SQL Server 2008 if the query has an existing unsafe expression. These run-time exceptions include the following:
Arithmetic exceptions: zero-divide, overflow, and underflow.
Conversion failures such as loss of precision and an attempt to convert a nonnumeric string to a number.
Aggregation over a set of values that are not all guaranteed to be nonnull.
In SQL Server 2000, these exceptions may not occur in a particular application that uses specific data. However, a query plan that is changed because of changing statistics potentially could lead to an exception in SQL Server 2008. You can prevent these run-time exceptions by modifying the query to include conditional expressions such as NULLIF or CASE.
Important:
Expressions that appear in a search condition, select list, or any other location within a query may be broken down and reorganized into one or more independent expressions. SQL Server may evaluate these independent expressions in any order with respect to each other. Filtering operations, including joins, are not necessarily applied before the result columns are computed.
I would suggest that with the introduction of a clustered index, and the size of the table, does in fact change the query plan, and whilst not an exception per se, has created an event where by the "reasonably expect" behaviour is a bit different. We all know that SQL 2008 has been updated in some of these areas, and possibly never really manifested itself until now.
Also, have a look at that reference : http://msdn.microsoft.com/en-us/library/ms191504.aspx
look at the tables between two boolean operands (one is horizontal, the other is vertical and the grid is the intersection of those outcomes).
Looking at the "OR" condition we had operand 1 = unknown and operand 2 = false -- net result = UNKNOWN
Now, looking at the AND condition, we have operand 1 = true and operand 2 (ie the above) = unknown -- net result = unknown, but I think being bracketed it is evaluating the first operand to be true.
Bottom line for me is it simply re-inforces the need to explicitly manage NULLs, and just another example (assuming that the query with isnull worked) and that's what the book says as well, and the reason being is to avoid unpredictable results such as you are experiencing.
However, would be worth raising a support case - mainly because the behaviour should be the same in a clustered or non clustered index, apart from the disclaimer that BOL says above (about changing query plans).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad you found it, it was highly suspect that it behaves differently for clustered vs non-clustered. I do stand by my postings, in so much as if you manage NULLs, then sometimes these things never surface.
Had a quick look at the KB - Is that the correct link ? Says it happens in 2005 as well, and is talking about pages being flushed...
Cheers,
Mark Wills
Had a quick look at the KB - Is that the correct link ? Says it happens in 2005 as well, and is talking about pages being flushed...
Cheers,
Mark Wills
ASKER
Mark,
yes thats the correct link. I did never experience that behavior in SQL 2005. Perhaps it came out in different situations only.
Thanks for your participation!
yes thats the correct link. I did never experience that behavior in SQL 2005. Perhaps it came out in different situations only.
Thanks for your participation!
And thanks for all the updates in this thread - I am sure it will prove valuable...