Clustered index=PK not returned by OpenSchema

  • Thread starter Thread starter onedaywhen
  • Start date Start date
O

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.

--
 
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.
 
I don't believe Jet uses Clustered Indexes, so it doesn't surprise me that
you're always seeing the property as being False.
 
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.
Click to expand...
</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...

--
 
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)


onedaywhen said:
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.
Click to expand...
</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.
Click to expand...
Click to expand...
 
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...
Click to expand...

....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.

--
 
Back
Top