Selecting id's from a huge database

Question

I have a database with over 3,000,000 rows, each has an id and xml field with varchar(6000).

If I do SELECT id FROM bigtable it takes +- 2 minutes to complete. Is there any way to get this in 30 seconds?

Answer 1

Build clustered index on id column

See http://msdn.microsoft.com/en-us/library/ms186342.aspx

Answer 2

You could apply indexes to your tables. In your case a clustered index.

Clustered indexes:

http://msdn.microsoft.com/en-gb/library/aa933131(v=sql.80).aspx

I would also suggest filtering your query so it doesn't return all 3 million rows each time, this can be done by using TOP or WHERE.

TOP:

SELECT TOP 1000 ID
FROM bigtable

WHERE:

SELECT ID FROM
bigtable
WHERE id IN (1,2,3,4,5)