questions about keys - porting code from MySQL to MS-SQL

[Ted]

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

[Roy Harvey]

Re: questions about keys - porting code from MySQL to MS-SQL

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@ro gers.comwrote:

>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

[David Portas]

Re: questions about keys - porting code from MySQL to MS-SQL

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

--

[Ted]

Re: questions about keys - porting code from MySQL to MS-SQL

Thanks Roy and David

I think Roy is right in postulating what I need to replace my "keys"
with is MS SQL's 'index'. I hadn't thought of that before because
MySQL also supports 'index'.

Yes, I am fully aware of foreign key constraints and their use (and I
routinely use them), but I have not encountered problems with them yet.


For additional reference, I have visited our nearest Chapter's looking
for references on Transact SQL, but I have yet to find one. I guess I
will have to break down and go to Toronto to look for such a reference
(a day trip in which half the time will be spent driving). BTW, I just
bought MS Visual Studio 2005 and so I am working with the developer's
edition of MS SQL. Does this help in making suggestions to narrow my
search for books to exclude unreliable or unusable books? What I would
find ideal is a book that thoroughly covers transact SQL, including the
fine details of what MS did and why, when options (such as clustering a
key) should be used and why, how MS has interpreted the standard and
what extensions they've provided, etc. Objective comparisons with
other RDBMS would be icing on the cake: useful but not necessary. ;-)
Has anyone seen such a book, or something reasonably close?

Thanks,

Ted

[David Portas]

Re: questions about keys - porting code from MySQL to MS-SQL

Roy Harvey wrote:

Clustering is a physical ordering of the table.

And the earth is flat and the moon is made of cream cheese... :-)

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

--