Post #212,134
6/22/05 3:59:46 PM
6/22/05 4:43:16 PM
|
Indexes and primary keys - redundant?
Given a table with a primary key set, is there any benefit in adding an index to the same column set, or are primary keys by default using a full-blown index? Some rumors are that primary keys are only "half-@ssed" indexed.
I am asking mostly about MS-SQL-Server, but am still curious about other vendor DB's.
________________
oop.ismad.com
Edited by tablizer
June 22, 2005, 04:43:16 PM EDT
|
Post #212,139
6/22/05 4:12:27 PM
6/22/05 4:16:09 PM
|
I can't recall every having an index on the primary key
unless this qualifies:
Client - Primary key client number
Orders - Primary key client number/Order number
- Index Client number/Order number in reverse order (for online listings showing most recent orders first)
A good friend will come and bail you out of jail ... but, a true friend will be sitting next to you saying, "Damn...that was fun!"
Edited by jbrabeck
June 22, 2005, 04:16:09 PM EDT
|
Post #212,171
6/22/05 6:57:51 PM
|
As I understand it
Declaring a primary key results in the automatic creation of a unique constraint on the primary key. To enforce the constraint, the dbms uses an index.
So its implied already.
Foreign keys, OTOH, do not automatically get indexed but can often benefit by the addition of a non-unique index - depending on access patterns.
If I am wrong, somebody please correct my understanding.
"Whenever you find you are on the side of the majority, it is time to pause and reflect" --Mark Twain
"The significant problems we face cannot be solved at the same level of thinking we were at when we created them." --Albert Einstein
"This is still a dangerous world. It's a world of madmen and uncertainty and potential mental losses." --George W. Bush
|
Post #212,545
6/25/05 9:30:22 AM
|
It's definitely the case with Oracle.
Two out of three people wonder where the other one is.
|
Post #212,175
6/22/05 7:24:06 PM
6/22/05 7:26:24 PM
|
The Primary Key is an Index.
So adding an Index that has the same definition as the Primary Key is generally frowned upon since it adds the overhead of another index, without adding any value. If you want to see the indexes, just modify the TableName in the following query: SELECT\n IndexName = RTRIM(ind.name),\n TableName = RTRIM(tab.name),\n ColumnName = RTRIM(col.name),\n KeyNo = RTRIM(sik.keyno)\nFROM\n sysindexes ind\n INNER JOIN sysindexkeys sik ON(\n (sik.indid = ind.indid))\n INNER JOIN syscolumns col ON(\n (col.colid = sik.colid))\n INNER JOIN sysobjects tab ON(\n (tab.id = ind.id) AND\n (tab.id = sik.id) AND\n (tab.id = col.id))\nWHERE \n (tab.name = 'MyTableName') \nORDER BY\n TableName,\n IndexName,\n ColumnName,\n KeyNo Also it's worth noting that a Primary Key may be NonClustered, electing to have one of the other indexes be Clustered.
Edited by ChrisR
June 22, 2005, 07:26:24 PM EDT
|
Post #212,190
6/22/05 10:40:41 PM
|
Conceptually no, in practice yes
In relational theory, a primary key is a table contraint. It specifies that the fields that make up the primary key are unique and not null and thus can be used to address every row in the table individually. This does not say anything about indexes, and conceptually they are unrelated.
In practice, every database I know of either creates an index automatically to enforce the constraint or considers a primary key to be a kind of index to begin with. Thus creating an index on a primary key would be waste in practice. MSSQL falls into the creates an index automatically camp,
It is possible that some database could have a special kind of index used for unique constraints that just indicates if a value exists already or not without containing a pointer to the actual row values. Such an index would optimize insert and update but do nothing for select. But I don't know of any database that does that.
Jay
|
Post #212,197
6/22/05 11:10:09 PM
|
Relational theory does not address indexes
as far as I know. Indexes are just an implementation (speed) aide. In theory one should get the same result rows from any given query regardless of whether indexes are in place or not. But, it appears an index of some kind is necessary in order to *efficiently* enforce the primary key constraint required by the theory.
________________
oop.ismad.com
|
Post #212,202
6/22/05 11:24:01 PM
|
Indexes are just tables....
...with the last field being a pointer to the record with the corresponding row in the full table. If the Index has full coverage, the query optimizer can get all the data from the index and never have to look at the full table. The primary reason for efficiency is due to have all the records in near vicinity to each other and in the correct order (as opposed to having to check every record scattered throughout the storage unit).
|
Post #212,260
6/23/05 10:36:27 AM
|
Re: just tables.
Unless they are clustered.
bcnu,
Mikem
It would seem, therefore, that the three human impulses embodied in religion are fear, conceit, and hatred. The purpose of religion, one might say, is to give an air of respectibility to these passions. -- Bertrand Russell
|
Post #212,221
6/23/05 1:11:11 AM
|
Check the index list.
Ask the server to list the indices for the table. If the primary key is included in that list, then it's highly likely that the primary key is an index with a unique constraint. This is so in MySQL, for instance.
Wade.
Save Fintlewoodlewix
|
