Clustered Indexes / Primary Key

Hi All,
I am converting an Access app. from .mdb to ODBC link to a SQL Server.
The first thing I have run into is Clustered Indexes not sorted. I have a
small lookup table with 2 fields. From what I was told here by our DBA,
every key has to have a primary key/clustered index. So, the lookup tables
first field is nothing but Category Numbers which are unique and created by
the user. The table has 121 records. The 2nd field is the Category Name.
The Cat. No. is the Primary Key, Clustered, Unique, No-Nulls. The Cat. Name
is indexed non-clustered. When you open the table, it is sorted by the Cat.
Name and not by the Primary Key! Why'
This is not the behavior I'm looking for. Just learning SQL Server.
DonThe only way you are guaranteed any type of sorting is by
using a query and using an order by clause. You don't want
to depend on a clustered index for sorting your data. You
use an order by.
-Sue
On Thu, 11 Aug 2005 07:59:02 -0700, "Donald King"
<DonaldKing@.discussions.microsoft.com> wrote:

>Hi All,
> I am converting an Access app. from .mdb to ODBC link to a SQL Server.
>The first thing I have run into is Clustered Indexes not sorted. I have a
>small lookup table with 2 fields. From what I was told here by our DBA,
>every key has to have a primary key/clustered index. So, the lookup tables
>first field is nothing but Category Numbers which are unique and created by
>the user. The table has 121 records. The 2nd field is the Category Name.
>The Cat. No. is the Primary Key, Clustered, Unique, No-Nulls. The Cat. Nam
e
>is indexed non-clustered. When you open the table, it is sorted by the Cat
.
>Name and not by the Primary Key! Why'
>This is not the behavior I'm looking for. Just learning SQL Server.
>Don|||Sue,
Thanks, I figured out that a Primary key DOES NOT have to be clustered
which I was misinformed by the DBA here. The ORDER BY clause in a query wil
l
execute faster I believe if the field that your ORDER BY clause refers to is
Indexed.
Tks again,
Don
--
"Sue Hoegemeier" wrote:

> The only way you are guaranteed any type of sorting is by
> using a query and using an order by clause. You don't want
> to depend on a clustered index for sorting your data. You
> use an order by.
> -Sue
> On Thu, 11 Aug 2005 07:59:02 -0700, "Donald King"
> <DonaldKing@.discussions.microsoft.com> wrote:
>
>|||I think the clustering PK issues hits people because if you
don't have a clustered index on a table and create a PK the
default is for it to be clustered. But it does not mean it
has to be that way - you can specify non-clustered for the
PK. And yes, if you are accessing the data sequentially that
column may be a good candidate for the clustered index. It's
always good to look at the whole picture with the table
though. You get one clustered index so its important to
choose wisely.
-Sue
On Tue, 16 Aug 2005 10:01:02 -0700, "Donald King"
<DonaldKing@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
> Thanks, I figured out that a Primary key DOES NOT have to be clustered
>which I was misinformed by the DBA here. The ORDER BY clause in a query wi
ll
>execute faster I believe if the field that your ORDER BY clause refers to i
s
>Indexed.
>Tks again,
>Don
>--
>"Sue Hoegemeier" wrote:
>