Posted by David Portas on 09/30/12 11:54

Ted 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


FOREIGN KEY is the standard keyword syntax that you need (ANSI SQL and
SQL Server). A foreign key is a *constraint*. It is not intended as a
performance optimization feature. For that you would use an index. I'm
aware that some versions of MySQL don't support foreign key constraints
so if you aren't fully familiar with the concept (you managed to
describe one without using the specific term) then I recommend you
first study some more about relational design principles to understand
just why keys and constraints are important. This is something much
more fundamental than just a different syntax.

You can find the full FOREIGN KEY syntax in SQL Server's Books Online
under the CREATE TABLE topic.

Clustered and Non-clustered are the two types of index supported by SQL
Server. The structure and use of indexes is a very big subject. The
best way to start to learn about it is to sit down with a good book and
make some notes as you go. Online material often skims the topic and
may be incomplete or misleading in some cases - and that includes
Microsoft's own documentation for SQL Server. Here are some reliable
sources:

http://www.amazon.com/gp/product/0735609985/sr=8-2/ref=pd_bbs_2/002-4788019-7287262?ie=UTF8
http://www.amazon.com/gp/product/0735623139/sr=8-4/ref=pd_bbs_4/002-4788019-7287262?ie=UTF8
http://www.sqlmag.com/Article/ArticleID/92886/sql_server_92886.html
http://www.sqlmag.com/Article/ArticleID/92887/sql_server_92887.html
http://www.sqlmag.com/Article/ArticleID/92888/sql_server_92888.html

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]