Error when using the IN Keyword on 100.000 elements
-
Hello,
I have a strange problem when using the IN keyword in a SQL query.
It works fine when the subquery generating the IN data returns 90.000 rows but as soon as it returns more than 96.000 rows (or something like that) it fails.
I have tried to use EXISTSinstead with a similar result.
JOIN is not an option since the subquery may or may not return more than 1 rows and I only want to be sure that I get atleast one row.
Any ideas?
/Thanks Joel
-
Humm nevermind, I found the problem.
My Query didn't return the rown in the same order every time. Doh.
-
Rows are never guaranteed to be returned in any specific order unless you specify an order with ORDER BY.
-
or you have a clustered index ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for the answers,
I forgot to add the ORDER BY clase I realised that
Still strange that I didn't get the data in order when nothing was updated, inserted or deleted.....I have a clusteder index (since primary key is always clustered)
But the order solved my problems (I'm creating a Page method and the order is therefore vital).
-
sushila: What do you mean "have a clustered index"? A clustered index does not guarantee a specific order of rows for a select query.
joel: Sure, it might seem strange that the order changes, but like I said there is nothing that guarantees a specific order. Temporary worktables, hashing, paralellism or lots of other factors might influence the order in which the query engine receives the rows from storage to return them.
-
Chris - doesn't a clustered index ensure that the rows are always returned in the order based on the indexed column ?!
Also - a primary key doesn't implicitly mean clustered!
**ASCII stupid question, get a stupid ANSI !!!** -
Run this and find for yourself :
CREATE TABLE [dbo].[TypesCommentairesADPS] (
[PkTypeCommentaireADP] [int] IDENTITY (1, 1) NOT NULL ,
[DescTypeCommentaireADP] [varchar] (50) COLLATE French_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TypesCommentairesADPS] WITH NOCHECK ADD
CONSTRAINT [PK_TypesCommentairesADPS] PRIMARY KEY CLUSTERED
(
[PkTypeCommentaireADP]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TypesCommentairesADPS] ADD
CONSTRAINT [IX_TypesCommentairesADPS_Unique] UNIQUE NONCLUSTERED
(
[DescTypeCommentaireADP]
) ON [PRIMARY] ,
CONSTRAINT [CK_TypesCommentairesADPS_DescTypeCommentaireADP] CHECK ([DescTypeCommentaireADP] '')
GO
Insert into dbo.TypesCommentairesADPS (DescTypeCommentaireADP) values ('Update')
Insert into dbo.TypesCommentairesADPS (DescTypeCommentaireADP) values ('Bug')
Insert into dbo.TypesCommentairesADPS (DescTypeCommentaireADP) values ('Amélioration')
Select * from dbo.TypesCommentairesADPS
/*
Index scan (IX_TypesCommentairesADPS_Unique)
PkTypeCommentaireADP DescTypeCommentaireADP
-------------------- --------------------------------------------------
3 Amélioration
2 Bug
1 Update
(3 ligne(s) affectée(s))
*/
ALTER TABLE dbo.TypesCommentairesADPS
drop constraint IX_TypesCommentairesADPS_Unique
GO
Select * from dbo.TypesCommentairesADPS
/*
Clustered index scan (PK_TypesCommentairesADPS)
PkTypeCommentaireADP DescTypeCommentaireADP
-------------------- --------------------------------------------------
1 Update
2 Bug
3 Amélioration
(3 ligne(s) affectée(s))
*/
drop table TypesCommentairesADPS
go
-
No
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
No - to which one Frank ?
**ASCII stupid question, get a stupid ANSI !!!** -
I have a table with an ID PK clustered index column...everytime I query it (regardless of whether my select includes the indexed column or any other combination of columns) I always get a "clustered index seek" and ALWAYS in the order of the clustered index column!
I've run different queries against this - used different databases and different tables ...I get the same results!
**ASCII stupid question, get a stupid ANSI !!!** -
Did you run my script???????
-
Remi - I KNEW you would ask that & I should've addressed that first...
No - I did not since I already had the setup to run queries...I can see what's happening in your script - after all you have the results displayed each time...
I am merely trying to understand why it works in my situation is all...
**ASCII stupid question, get a stupid ANSI !!!** -
No - to which one Frank ?
Did you ask more than one question in that posting?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I would suggest you buy a book that explains how sql server's optimizer works under the hood... maybe inside sql can help you. But Frank would be better at suggesting a book...
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply