sql primary key and index

sql sql-server tsql indexing primary-key
danifo picture danifo · Jan 20, 2009 · Viewed 100.6k times · Source

Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed?

Reason I ask is because in MS SQL Server I can create an index on this ID, which as I stated is my primary key.

Edit: an additional question - will it do any harm to additionally index the primary key?

Answer

dkretz picture dkretz · Jan 20, 2009

You are right, it's confusing that SQL Server allows you to create duplicate indexes on the same field(s). But the fact that you can create another doesn't indicate that the PK index doesn't also already exist.

The additional index does no good, but the only harm (very small) is the additional file size and row-creation overhead.

Related questions

Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?

Read More
How important is the order of columns in indexes?

I've heard that you should put columns that will be the most selective at the beginning of the index declaration. Example: CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) First off, is what I'm saying correct? If so, am …

Read More
When should you use full-text indexing?

We have a whole bunch of queries that "search" for clients, customers, etc. You can search by first name, email, etc. We're using LIKE statements in the following manner: SELECT * FROM customer WHERE fname LIKE '%someName%' Does full-text …

Read More