473,243 Members | 1,613 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,243 software developers and data experts.

Compound Primary Key - order not as expected

Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk

it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?

Apr 26 '06 #1
9 4460
John Rivers wrote:
Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk
This is not wrong at all. As long as you do not have an "ORDER BY"
clause the RDBMS is free to return records in *any* order.
it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
Again: you have to adjust your expectations.
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?


Yes.

Cheers

robert

Apr 26 '06 #2
Order is not guaranteed unless you include an ORDER BY. This is by design.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"John Rivers" <fi*****@btinternet.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...
Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk

it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?

Apr 26 '06 #3
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

You need to read a book on RDBMS; you are still locked into a file
system mind set.

Apr 27 '06 #4
Hello,

when a clustered index is present the records *are* physically ordered
on disk to match the index

that is the whole point of a clustered index

and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)

this can easily be proved by watching the Execution Plan

the issue i am trying to highlight concerns the order of the records on
disk when a *compound* clustered index is present

i have seen cases when it is not as expected

maybe you can enjoy reading that RDBMS book :-)

best wishes,

john

Apr 28 '06 #5
John Rivers wrote:
Hello,

when a clustered index is present the records *are* physically ordered
on disk to match the index

that is the whole point of a clustered index

and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)

Um. No. I've seen it return them out of order with only a few hundred
rows. As soon as the table is occupying more than one page, the query
optimizer *can* decide to produce a parallel plan. You'll see the
result as chunks of output which are in clustered index order, but no
deterministic ordering between the chunks. e.g. it'll look like:

1
2
3
4
5
11
12
13
14
15
6
7
8
9
10

The *only* way to guarantee the order of output is to put an order by
clause on your select statement.

Damien

Apr 28 '06 #6
John Rivers wrote:

and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)


Not true at all. As Joe says, tables are not logically ordered. There
is no guarantee that any queries will match the physical order on disk
or in a clustered index.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Apr 28 '06 #7
On 28 Apr 2006 05:41:26 -0700, John Rivers wrote:
Hello,

when a clustered index is present the records *are* physically ordered
on disk to match the index

that is the whole point of a clustered index
Hi John,

Correct.
and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)
Incorrect. Damien already pointed out the risk of parallellism.

Another potential issue is an optimization technique MS employs called
"piggybacking" - if a query on another connection is in the middle of a
tbale scan on the table you need, the DB will use the values coming in
for your query as well, then (when the first query's table scan is
finished) restart the scan from start up to where it started to
piggyback. The results would be like 6 - 7 - 8 - 9 - 10 - 1 - 2 - 3 - 4
- 5

This is almost impossible to reproduce in a test environment, but it
MIGHT happen intermittently in a heavily used production DB. Tough lluck
if your app expects the rows to be in order, even without ORDER BY.
the issue i am trying to highlight concerns the order of the records on
disk when a *compound* clustered index is present

i have seen cases when it is not as expected


How did you "see" those cases? Using a query reallly doesn't prove
anything. Did you issue DBCC PAGE commands to inspect the actual
contents of the index and data pages?

--
Hugo Kornelis, SQL Server MVP
Apr 28 '06 #8
John Rivers (fi*****@btinternet.com) writes:
when a clustered index is present the records *are* physically ordered
on disk to match the index

that is the whole point of a clustered index
Actually, they are ordered if you follow the page links. But if pages
are in disorder, the physical order on disk may be yet another one.
and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)


No. This may have been true by chance for SQL Server up version 6.5. It is
definitely not correct for SQL 7 and later.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 28 '06 #9
Thanks for your knowledgable answers

I will check out DBCC PAGE

Apr 29 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Damir Mikoc | last post by:
Hi all, I work on some J2EE application in Oracle JDeveloper. But my problem is that Oracle's OC4J does not support CMP entity beans with CMR and Compound Primary key. My simple question is:...
2
by: skidvd | last post by:
Hello: I have just recently converted to using the InnoDB table type so that I can enforce FOREIGN key constraints. I have been using MyISAM tables (accessed via JDBC) successfully for some...
4
by: Mark | last post by:
BEGINNER QUESTION I have a table which has a compound primary key consisting of two columns. One of these columns is a foreign key which is generated in another table by an identity. I want...
7
by: Philip | last post by:
Hey all, (Access 2000) I've been having a horror story with this design problem. My Database is Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each Item in each table...
6
by: David W. Fenton | last post by:
I'm generally against using compound keys, except in join tables, but I'm currently mapping out a schema where the join table has child records. The application is for fund-raising and I have four...
7
by: Eric Laberge | last post by:
Aloha! This question is meant to be about C99 and unnamed compound objects. As I read, if such a construct as int *p = (int){0}; is used within a function, then it has "automatic storage...
2
by: Todd Webb | last post by:
I would like to talk to the COM implementation of Structured Storage from C#. I would like a C# equivalent of StgCreateStorageEx. I expected to find support in the stdole Primary Interop...
8
by: wespvp | last post by:
I am using PostgreSQL 7.4.1 on RedHat 7.2. The query I am executing is something like (I replaced all the return values with 'count'): db=> explain select count(*) from messages m join (select...
4
by: vincibleman | last post by:
Howdy all, Working my way into SQL from Access. Think I might have the hang of the basics, but would really appreciate a sanity check. The stored procedure listed below works, but I can't help...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.