heap or clustered

Question

heap or clustered

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

radek (4/12/2009)
BTW: table like product_type, product,product_group,failure_cause etc.. is small max 2000rows and tbl_FAILURES_archive_only_first_entrys is about 4000, I think thats very small to create indexes but statistics is better concrete logical read then maybe not 🙂

Indexes can be beneficial on tables far smaller than 2000 rows. There's no threshold value where one can say 'index if larger than this'. You have to test and see.

I'll take a look at the revised exec plan tomorrow. Is past 10pm here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

radekhatle SSC Eights! Points: 955 More actions · Answer 2

GilaMonster (4/12/2009)
radek (4/12/2009)
BTW: table like product_type, product,product_group,failure_cause etc.. is small max 2000rows and tbl_FAILURES_archive_only_first_entrys is about 4000, I think thats very small to create indexes but statistics is better concrete logical read then maybe not 🙂
Indexes can be beneficial on tables far smaller than 2000 rows. There's no threshold value where one can say 'index if larger than this'. You have to test and see.
I'll take a look at the revised exec plan tomorrow. Is past 10pm here.

Hm, interesting.

So I will test it.But now its much better then before now its take about 3sec before it takes more then 5minutes There is too past 10PM.

So good nigh and thanks

nawillia SSCrazy Points: 2401 More actions · Answer 3

Hello,

I know this is an old post, and i've done some research in BOL and other websites, but no where can I find an answer to this question. I have a table that i use to archive some data. Each time I archive the data I use a timestamp. So the archive table is insert only. When users need data, they most likely need to query all the data by a particular date or some other single field. So initially I left a primary key off the table and just put in specific non clustered indexes that would be queried in the where clause, like archive_date. My reasoning for not having the clustered index was to limit the import process.

Another DBA requested I put the PK on the table, which I obliged, but I was 1. wondering what do you call a table with no PK, but non clustered indexes, is it still a heap.

2. the other DBA said that heap is by definition fragmented. I thought a table would only be fragmented if inserts/updates/deletes happened, not to insert only tables. Which is correct?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Could you please post new questions in a new thread? Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass