473,231 Members | 3,032 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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

Aug 2 '06 #1
4 1831
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.*********@rogers.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
Aug 2 '06 #2
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/073...287262?ie=UTF8
http://www.amazon.com/gp/product/073...287262?ie=UTF8
http://www.sqlmag.com/Article/Articl...ver_92886.html
http://www.sqlmag.com/Article/Articl...ver_92887.html
http://www.sqlmag.com/Article/Articl...ver_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/m...S,SQL.90).aspx
--

Aug 2 '06 #3
Ted
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

Aug 2 '06 #4
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/m...S,SQL.90).aspx
--

Aug 2 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: John Simmons | last post by:
How is it that even though I have the column "username" in my database set as a Primary key, using my PHP script to add new users to the database works without any errors even when signing up using...
3
by: Jonathan | last post by:
I have to port a MS Access DB to anotherdatabase engine and I would like to use MySQL because I am familiar with it and is easy to integrate with the web and php what is the ned point of my...
11
by: Errol Neal | last post by:
Hi all, Not sure if this is a question for a php list or this one, but I'll give it a shot and if I am wrong, please do not crucify me. :-) There is a php based sourceforge project called...
4
by: Chris Travers | last post by:
Hi all; A few years ago, I set about porting a PHP application from MySQL to PostgreSQL, after realizing that MySQL wasn't going to be able to handle it. In order to do this, I built a light,...
2
by: David | last post by:
Hi, Has anyone had this problem ? I am using MySQL ODBC 3.51 Driver, with MS Access 2003 and MySQL 4.1.11 standard log. I created my tables in MS Access, then exported them via ODBC to an...
16
by: eSolTec, Inc. 501(c)(3) | last post by:
Thank you in advance for any and all assistance. I'm looking for a way to programmatically retrieve the following if possible: Windows Installation Key or COA from the registry Windows installed...
31
by: anon856525 | last post by:
Hello I am seeking the best way (speed and portability) to program mode 13h (320 x 200 256 colors), and mode X. I am using Borland's Turbo C ver 3.0 for MS DOS. With Borland product, I can...
2
blyxx86
by: blyxx86 | last post by:
Good Afternoon Everyone, I am migrating from MS Access to MySQL (TONS faster, not to mention the usefulness of triggers). Well, I am running into problems with the way MS Access lets you design...
22
by: Jesse Burns | last post by:
I'm about to start working on my first large scale site (in my opinion) that will hopefully have 1000+ users a day. ok, this isn't on the google/facebook scale, but it's going to be have more hits...
1
ssnaik84
by: ssnaik84 | last post by:
Hi Guys, Last year I got a chance to work with R&D team, which was working on DB scripts conversion.. Though there is migration tool available, it converts only tables and constraints.. Rest of...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.