473,847 Members | 1,523 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1882
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.*********@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
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
3593
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 an existing username. I have a database full of the same usernames!
3
2172
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 'quest'. However there are paramter queries in the access database and I would really like to keep the access database as a backup as long as I'm in the progress of moving/porting. I have taken a look at the ODBC connector (3.51) and have tried a...
11
2951
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 mailwatch. (http://www.sourceforge.net/projects/mailwatch) It logs data from the excellent Mailscanner security product into a mysql database. Now, I am not a php programmer,
4
2402
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, fast database abstraction layer which conforms to the behavior of the MySQL functions in PHP. This means that a large amount of porting work could be made simple using this porting layer if the application was originally used PHP's native MySQL...
2
2719
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 externally hosted MySQL database (fasthosts) . I then import-linked
16
1851
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 applications, like: MS Office 97-2003 MS FrontPage MS Word MS Works
31
2045
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 not find a BGI driver for this mode. I know there are built in support for the other VGA modes (640 x 200 x 16 colors), (640 x 350 x 16 colors).. but I am not interested int those.
2
5050
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 and the way MySQL designs tables. I test the joins and relationships with an ODBC Connection and linking the tables from within MS Access to my MySQL database. My question is within the PK-FK relationships. I usually would have one...
22
3689
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 than just family and friends. Either way, I'm planning on this site blowing up once I have enough of a feature set, so I'm concerned about performance and scalability in the long run. I've worked for a software company, but I've never...
1
9600
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 things (stored procedures, functions).. we have to manually edit. That time, we face some interesting challenges.. I failed to document all of them, but whatever I can share with u.. I will try.. :) ...
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9735
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10658
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10347
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9497
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5730
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4541
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.