>>When i ran count(*) it gave me result immed. but select * from table still took 46 seconds

The test is only valid if you DON'T have a non-clustered index on the table. If you have an NC index, SQL Server can get the row count by scanning the leaf-level of the index.


>> when i ran dbcc Showcontig it prints following information
>- Scan Density [Best Count:Actual Count].......: 96.72% [59:61]

Well, you're table is not fragmented (we'll not anything to worry about).

>> - Avg. Bytes Free per Page.....................: 2389.6

The rows of this table must be very large. A page is ~8K and this stat. shows that over a quarter of all pages (61) are empty.

-Mike