Error when using the IN Keyword on 100.000 elements
-
Yes - the second one was that defining primary keys do not necessarily mean a clustered index UNLESS (cutting & pasting from BOL to make life easier....):
"IF a clustered index does not already exist on the table, OR a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint"
**ASCII stupid question, get a stupid ANSI !!!** -
In my query ,in which I had one clustered index, It returned the same order EVERY time until i had more than 98.000 rows returned. After that I got random order.
-
Okay, must have missed the question mark then.
Yes, but you must explicitely create the PK nonclustered, otherwise SQL Server will make it the clustered index.
There is no book needed. Chris already mentioned potential "troublemakers" here. To repeat the old litany. The only reliable way to get the ordering you expect, is to use an ORDER BY.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Umm to clarify my last post.
ORDER must be used to be sure.
I only tried to explain that you have to have ALOT of rows to find that out in tests
-
Create the query on a single-processor machine and let it run on a multi-processor machine. if the optimiser decides for a parallel plan, I am curious on your ordered resultset
http://support.microsoft.com/default.aspx?scid=kb;EN-US;273586 is also interesting.
Btw, one can find in BOL:
Expect different results as compared to earlier versions of SQL Server. Add an explicit ORDER BY clause to all SELECT <JavaScript:hhobj_1.Click()> statements needing to produce ordered rows.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Joel - thanks for throwing that in - I certainly do not have more than a few thousand rows in all the tables I've tested so far...
**ASCII stupid question, get a stupid ANSI !!!** -
And just to add my $.02. From Book Online:
ORDER BY order_list [ ASC | DESC ]
The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted in an ascending or descending sequence.
ORDER BY is important because relational theory specifies that the rows in a result set cannot be assumed to have any sequence unless ORDER BY is specified. ORDER BY must be used in any SELECT statement for which the order of the result set rows is important.
-
>ORDER BY is important because relational theory specifies that the rows in a result set cannot be assumed to have any sequence unless ORDER BY is specified.
Hmm, maybe they should have written that "the SQL standard specifies ...". Relational theory specifies that the rows form a set, and a set is by definition unordered. It is not possible to order the rows in a set! Ordered rows are instead a tuple bag, or simply a list.
-
It's been a laborious and painstaking exercise but I spent all morning creating and testing a dummy table with 3 combinations of 2 columns - clustered and unique nonclustered; clustered and nonclustered & clustered only:
In the first 2 instances (clustered and nonclustered (with unique constraint) AND clustered and nonclustered), "selecting *" gave me resultset ordered by nonclustered index and "selecting * with where clause on clustered index" gave me a resultset ordered by clustered index.
When I had only the one clustered index on table and queried it I always got the resultset ordered by indexed column (regardless of "select *" or "select * with where clause")
I haven't gotten to a point where I add several more indexes on the table and see what indexes have scanning priority!
This exercise may seem very basic to the likes of Chris, Frank & Remi but follow the link here to read the article on this site on the orderby clause and the conclusion the author reaches.....
http://www.sqlservercentral.com/columnists/gvijayakumar/whentousetheorderbyclause.asp
Lastly - getting back to what Joel found - that the orderby was always per clustered index till he hit x # of rows...how does that fit in with all this ?!
Maybe one of you could undertake to write another article (as a sort of addendum to the one posted in the link) to explain further the sequence of index scans when there are 2 or more indexes in a table with combinations of clustered and/or non ?! (mais Remi - pas en français s'il vous plaît <;-)
Or is this a request I should send to Steve/Andy ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Why do you say that?? I Haven't spoken a word of french since I'm here...
-
(3 ligne(s) affectée(s))....
No - you've never spoken it....just copied and pasted sometimes with french thrown in....
& before you take this too seriously...I was only teasing when I said that!
**ASCII stupid question, get a stupid ANSI !!!** -
Ya... can't do much about that... My boss bought the software in french.
And I didn't take it personal.
-
Alors...then can we move on to my original request for additional article/s on order/priority of clustered/nonclustered index scanning order?!
Chris - I get the impression that you're in cahoots with Andy/Steve - could you forward this request to them and/or write it yourself ?!
**ASCII stupid question, get a stupid ANSI !!!** -
You cannot depend on any index to return the data in any specified order for at least these two reason:
1) Your are depending on the current retrieval implementation method of the software and this may change.
2) At some point in time the index may be dropped or modified. For example someone may decide there is a better column to place the clustered index.
If you really must have the data in a specific order then you must use the Order By clause.
-
I personally don't have time to write one... So It's gonna have to be somebody else.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply