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 4 1835
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
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
--
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
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
-- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |