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
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
IndexName = RTRIM(ind.name),
TableName = RTRIM(tab.name),
ColumnName = RTRIM(col.name),
KeyNo = RTRIM(sik.keyno)
FROM
sysindexes ind
INNER JOIN sysindexkeys sik ON(
(sik.indid = ind.indid))
INNER JOIN syscolumns col ON(
(col.colid = sik.colid))
INNER JOIN sysobjects tab ON(
(tab.id = ind.id) AND
(tab.id = sik.id) AND
(tab.id = col.id))
WHERE
(tab.name = 'MyTableName')
ORDER BY
TableName,
IndexName,
ColumnName,
KeyNo
