| Author |
Topic |
|
phpfreak
Starting Member
1 Post |
 Posted - 2002-08-02 : 10:07:24
|
| Is it possible to have a huge database without any index. Why do we need an index? I have read that a related database is good because they have indexes. What is an index? |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-02 : 10:23:14
|
| Imagine the Library of Congress without the Dewey Decimal System....Sure, you can find "Zen and the Art of Motorcycle Maintenance", but you're going to have to look at every book in the library . . .Jay White{0} |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-02 : 10:26:57
|
Jay, as always, is absolutely right.  Indexes are also used to provide data integrity by enforcing unique constraints and primary keys. Without a primary key on a table, you really don't have a table, because there is no way to identify each row uniquely (at least no way to ENFORCE that unique identification)Before you get anywhere involved with designing a database you need to get a book on database design and read it. No offense, but "Is it possible to have a huge database without any index?" is the kind of question that causes a great deal of shuddering here. If you don't learn more and design the database right you'll have tons of problems in the future. |
|
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-08-02 : 12:25:07
|
quote:
Without a primary key on a table, you really don't have a table, because there is no way to identify each row uniquely (at least no way to ENFORCE that unique identification)
i respectfully disagree with the first part -- of course you can have a table without a primary key!and the second part is iffy too -- you can enforce unique identification through a UNIQUE constraintbut i will concede that i'm really just splitting hairs  there are many decent database design resources on the web, e.g. the Data Modeling course at UT Austin [url]http://www.utexas.edu/cc/database/datamodeling/[/url]rudyhttp://rudy.ca/rudyhttp://rudy.ca/ |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2002-08-02 : 12:35:50
|
quote:
you can enforce unique identification through a UNIQUE constraint
A unique constraint can cover a column that allows NULLs. So, rows with values are guaranteed unique, but you could have n rows that have no values. A primary key is a unique constraint that does not allow NULLs.To me, a table without a primary key is nothing more than a pile of sticks.-- monkeyEdited by - monkeybite on 08/02/2002 12:36:49 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-02 : 12:42:30
|
quote:
...of course you can have a table without a primary key!...
I'm sure you know how Celko feels about this statement...  ...Jay White{0} |
|
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-08-02 : 12:54:08
|
quote:
So, rows with values are guaranteed unique, but you could have n rows that have no values.
Monkeybite, I definately agree with you on tables without pk's, but you can only have one (not n) null in a field with a unique constraint. Actually this doesn't really make sense to me because I always thought that null != null, but that's the way it's implemented.Edited by - joshb on 08/02/2002 12:56:09 |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2002-08-02 : 13:04:43
|
quote:
you can only have one (not n) null in a field with a unique constraint.
I stand corrected, joshb, thank you. I was thinking the same thing you intonated, NULL <> NULL, not about actual implementation in SQL Sever ...-- monkey |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-08-02 : 13:07:29
|
quote:
To me, a table without a primary key is nothing more than a pile of sticks.
Good for burning up your CPUs? |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-02 : 13:30:36
|
| Why would anyone need to not have and index?...deep thoughts, by...Jay White{0} |
|
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-08-02 : 13:37:28
|
| Mabey if a person had a very masochistic personality. |
|
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-08-02 : 16:05:33
|
quote:
I'm sure you know how Celko feels about (a table without a primary key)
it was celko, via one of his columns, long ago, who first introduced me to the "three tins of cat food" exampleif you want to get picky, you cannot have a relation without a primary keyyou might argue that you cannot have a set without a primary key either, even if the primary key is virtual or unidentifiedbut in a database, you can definitely have tables without primary keys -- happens in real life all the time (e.g. in sql/server before RI support was added)not that i advocate not defining primary keys, you understand, i simply acknowledge that it happens all the time, and often for good reasonrationale: if you don't need FKs checked by the dbms, you don't need a PK by the way, i'm pleasantly surprised at the way you people have defended good design i thought primary keys and stuff like that were of importance only to RI fanatics and data modellers, and that i might have to explain them to a sql/server crowdbadump-bumpJUST KIDDING! IT'S FRIDAY!!rudyrudyhttp://rudy.ca/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-02 : 17:03:22
|
quote:
not that i advocate not defining primary keys, you understand, i simply acknowledge that it happens all the time, and often for good reason
No.It happens "all the time" 100.000% of the time because people have no idea how to design a database properly. There IS NO GOOD REASON EVER to not have a primary key. I will gladly listen to whatever argument you might make otherwise, but I will never agree with it.quote:
rationale: if you don't need FKs checked by the dbms, you don't need a PK
What???? You might as well argue there's no good reason to wear a condom because the guy and the girl are both virgins. And guess what? You don't even NEED a primary key to declare a foreign key...just a unique index, if you want to keep splitting hairs. The "pile of sticks" view is absolutely right on. If you don't have a primary key, don't use SQL Server, just put everything in Excel or text files, then you don't have to deal with other unnecessary things like SQL. |
|
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-08-02 : 17:49:35
|
quote:
There IS NO GOOD REASON EVER to not have a primary key. I will gladly listen to whatever argument you might make otherwise, but I will never agree with it.
where's the smiley? without the smiley, that sounds awfully close-minded, it's like you have pre-judged whatever i could offer, before i've even suggested it... that's called prejudicefor what it's worth, i'll try to restate the good reason -- if there's no relationship, i.e. no child table, i.e. no foreign key pointing to the primary key, then there's no need for the primary keyquote:
You don't even NEED a primary key to declare a foreign key...just a unique index
hey, you're right, thanks, i wasn't aware of that... and you're right, it is splitting hairs, but i appreciate it, because i like to get the facts straight, especially if it's me that's got them wrong and your last comment was so over the top i'm just going to ignore itrudy (been using sql for 15+ years)rudyhttp://rudy.ca/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-02 : 18:31:15
|
Well, here's the question from another angle: are there any DISADVANTAGES to having a primary key? If there are, then I would like to hear them. My feeling is that if there's no disadvantage, meaning no harm in having a primary key, then why NOT have it?Yes, I am short-sighted and close-minded (SHUT UP FEMENELLA!) but in this particular case I've seen many, many compelling reasons for always having a primary key, but not a single one against it. I have no problem being prejudiced whenever it will further good database design. I've only been using SQL Server for about 4 years, but I learned this over 6 years ago using Microsoft Access. I think you'll agree Access is nowhere near a relational database system, but even it promotes the necessity of primary keys. IIRC I've even seen this advice in some old Clipper books of mine!quote:
if there's no relationship, i.e. no child table, i.e. no foreign key pointing to the primary key, then there's no need for the primary key
If that's really the case, I would submit that there's no DATABASE then...and therefore using Excel or text files is just as valid as using SQL Server...more so, in fact. It's nothing but a heap of data with nothing to distinguish one "row" from the next.And to clarify the condom analogy  there's more than one reason to use one, as there are other reasons where a primary key can serve besides foreign keys/relations. |
|
|
|
CMartin
Starting Member
13 Posts |
Posted - 2002-08-03 : 02:15:38
|
Hi all... quote:
Well, here's the question from another angle: are there any DISADVANTAGES to having a primary key? If there are, then I would like to hear them. My feeling is that if there's no disadvantage, meaning no harm in having a primary key, then why NOT have it?
DISADVANTAGES:1. If you create a Unique Clustered PK in a small table that has 500 or less rows, it will take longer to find a row by using the index than by doing a simple table scan.2. If you create a Unique Custered PK on a field with more than 100 bytes you certanly will have performance problems when the table starts to grow and if you frequently update/delete/query the table3. and other problems if you use PK where you shouldn't------------------I always use PKs and Indexes99.5% of the times I use a PK and sometimes also indexes in other fields that I'll use to perform queries to the table.In tables that I don't have a PK (very, very rare) I always create an index in the field I'll use often to do queries.From the SQL BOL:"The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can impair the performance of the entire system.[...]The fundamental rule of database design theory is that each table should have a unique row identifier, a column or set of columns that can be used to distinguish any single record from every other record in the table. Each table should have an ID column, and no two records can share the same ID value. The column or columns serving as the unique row identifier for a table are the primary key of the table."Well I couldn't said better to justify the use of PKs and Indexes.How to use PKs and indexes, it all depends on how your database/tables are designed and will be used.Carlos |
|
|
|
|
Why do we need INDEX?