Posted by Ted on 10/15/67 11:54

Understand, I have developed a number of applications using RDBMS,
including MySQL, PostgreSQL and MS Access, but this is my first
experience with MS SQL. I'd bet my bottom dollar that MS SQL supports
what I need, but I just haven't found where it is explained in any
detail in the documentation I have. The pages I have found strike me
as a little too terse for my needs.

In MySQL, I used statements like:
PRIMARY KEY (`ic_contact_id`),
KEY `ic_planner_id_k_tmp` (`ic_rep_code`)

at the end of the SQL statement that creates a table. The primary key
had to be unique but the other did not. Defining the non-unique key
paid huge dividends in the performance of certain queries, sometimes
leading to orders of magnitude improvement compared to when the KEY was
not defined (a few seconds vs tens of minutes). In joins, these keys
relate to primary keys in other tables that function as lookup tables.
Otherwise, their primary role is for aggregation functions (max, min,
&c.) in relation to group by clauses. The performance improvements
from having the KEYs defined are greatest in the latter.

I have learned the hard way that MS SQL seems to like my primary key
clauses but not my KEY clauses. I don't know, and at present don't
care, if this is because MySQL supports my KEYs as an extension to the
standard, or if it is a matter of the two RDBMS interpreting the
standard differently, or something else. What I need to know right now
is how do I obtain in MS SQL the same benefit as the MySQL KEY provided
to me.

A second question is that, in studying the documentation for the create
table statement, I saw reference to clustered vs non-clustered keys (at
least I assume they relate to keys since they immediately follow, and
are indented from, the primary key and unique keywords). What exactly
is clustered and why? BTW, my primary understanding of "clustering"
derives from work with numerical taxonomy and biogeography, but I'd
wager that is something completely different from any clustering done
in an RDBMS.

I'll appreciate any clarification you can provide.

Thanks,

Ted

[Back to original message]