|
|
Posted by Roy Harvey on 10/14/80 11:54
Clustering is a physical ordering of the table. The leaf level of a
clustered index is the actual data page of the table itself.
By default, a Primary Key will be clustered. You can override this,
or if there is already a clustered index in place before the PK is
defined the PK will be non-clustered.
The other "keys" you talk about sound like indexes in SQL Server. See
the CREATE INDEX command. Indexes may be unique (or not), clustered
(if there is no other clustered index defined) (or not).
That should be enough to get you started.
Roy Harvey
Beacon Falls, CT
On 2 Aug 2006 12:27:47 -0700, "Ted" <r.ted.byers@rogers.com> wrote:
>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
Navigation:
[Reply to this message]
|