question about heap tables 1

[mollytu]

I'm seeking clarification. I've seen heap tables defined as tables with no CLUSTERED index, but can have NON-clustered indexes on them. However, it does seem that SQL Server makes a distinction between these definitions. As in the sysindexes table, indid 0, 1, 2 are for no-indexes, clustered index, and tables with a regular index.

Also my other question is, I seem to recall running Sajal Dam's script (from his SQL Server Performance Tuning book) for re-indexing indexes on tables that have a fragmentation of a certain threshold. However, I notice that this script NEVER worked on tables unless there was a clustered index. Anyways, does anyone know what I'm talking about? I think it was running DBCC INDEXDEFRAG, but it just completely skipped the "heap" tables, that that I mean tables with out a clustered index, but may have had a regular non-clustered index.

Thanks!

 

[NoCoolHandle]

Ok..

a heap is a table with no clustered index.

i.e. data can go anywhere there is a spare space.
(index id=0)
If you add a clustedd index. THe data has to go in in the order defined in the index. (Like a dictonary)
(index id=1)
A table will never have both index id 0 and 1. it is one or the other.

If you have a clusted index on last name in ascending sort(the default). THe first name will be aardvark, the last zythromx

Every insert must go in in the order or the clusterd index. This can cause table fragmentation as pages need to split when they are full.


I don;t know about the script you are talkin about, but if you do a seach in bol for dbcc showcontig, you will find a good script that with a small amount of customization will do a very good job or reindexing or defraging an index based on the level of fragmentation. Rule of thumb. over 30% rebuild. 30% or less defrag. less than 10% don't waste your time. (or sql servers) rebuildning statistics will almost always be ok.

HTH


Rob
(PS sorry about the typos.. to many beers and too late at night )

 

[NoCoolHandle]

Oh yeah.. 1 last thing index id 2 through 250 is a non clusterd index
254(or 255 can't remember) = image or text datatype - maybe even \overflow for varchar(max) or varbinary(max)

As data over 8k is stored off the page in a b-tree index

R