Clustered index=PK not returned by OpenSchema

[onedaywhen]

I'm using ADO's OpenSchema method with adSchemaIndexes on a Jet 4.0
..mdb database. For each index found, in the resulting recordset the
CLUSTERED field is 'false' even when the PRIMARY_KEY field is 'true'.
It is my understanding that for Jet a table's primary key is always
the clustered index (I wish I was wrong on this one). Can anyone shed
any light on this discrepancy e.g. is this known behavor of the OLE DB
provider for Jet?

Many thanks.

--

 

[onedaywhen]

No takers? Let me try again.

Consider this code, executed from a MS Access module within a new
blank database (questions follow):

'<Code>--------
Option Explicit

Sub Test()
Dim Con As ADODB.Connection
Dim rs As ADODB.Recordset
Set Con = CurrentProject.Connection
With Con
On Error Resume Next
.Execute "DROP TABLE Table1"
On Error GoTo 0
.Execute "CREATE TABLE Table1" & _
" (Col1 INTEGER PRIMARY KEY)"
Set rs = .OpenSchema(adSchemaIndexes)
If Not rs.EOF Then
MsgBox rs!INDEX_NAME & _
": PK=" & CStr(rs!PRIMARY_KEY) & _
", Clustered=" & CStr(rs!Clustered)
End If
End With
End Sub
'</Code>-------

1. Am I correct in assuming the primary key will be the clustered
index for the table?
2. If so, does anyone know why the OpenSchema shows PK=True and
Clustered=False?

Many thanks.

 

[Douglas J. Steele]

I don't believe Jet uses Clustered Indexes, so it doesn't surprise me that
you're always seeing the property as being False.

 

[onedaywhen]

Thanks for your reply.

Here's where I'm coming from:

http://groups.google.com/[email protected]&rnum=1

In Jet the PK is always the clustered index. You have no
choice in the matter ... one of the strongest arguments against an
Autonumber PK [in Jet] is that a unique, monotonically increasing integer
makes a poor choice for a clustered index.

</quote>

I guess my interpretation of CLUSTERED=False was too literal. Perhaps
it is simply a case of CLUSTERED=unsupported being 'coerced' as a
boolean to be CLUSTERED=False.

But I'm now wondering if it is indeed correct that the PK always
dictates the physical order on disk. I think a new thread is in
order...

--

 

[Douglas J. Steele]

AFAIK, the PK does not dictate the physical order on the disk, but I can't
find any references to confirm or refute that statement.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks for your reply.

Here's where I'm coming from:

http://groups.google.com/[email protected]
e.net&rnum=1

In Jet the PK is always the clustered index. You have no
choice in the matter ... one of the strongest arguments against an
Autonumber PK [in Jet] is that a unique, monotonically increasing integer
makes a poor choice for a clustered index.

</quote>

I guess my interpretation of CLUSTERED=False was too literal. Perhaps
it is simply a case of CLUSTERED=unsupported being 'coerced' as a
boolean to be CLUSTERED=False.

But I'm now wondering if it is indeed correct that the PK always
dictates the physical order on disk. I think a new thread is in
order...

--

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message

I don't believe Jet uses Clustered Indexes, so it doesn't surprise me that
you're always seeing the property as being False.

 

[onedaywhen]

I'm now wondering if it is indeed correct that the PK always
dictates the physical order on disk. I think a new thread is in
order...

....no need, here's the answer:

http://support.microsoft.com/default.aspx?scid=kb;en-us;137039
New Features in Microsoft Jet Version 3.0

<quote>
"Compacting the database now results in the indices being stored in a
clustered-index format... based on the primary key of the table ...
the clustered index isn't maintained until the next compact... New
data entered will be in time order."
</quote>

So Jet *does* use clustered indexes, it just doesn't expose them.

--