Database Question

JC724

[H]Lite
Joined
Jan 20, 2016
Messages
99
Can some one please explain to me the difference and the pros and cons of a HEAP FILE, HEAP BASED INDEX, CLUSTERED and UNCLUSTERED INDEX?

I haven't been getting clear answers from google which probably means I am really confused in general.
 

modi123

[H]ardness Supreme
Joined
Sep 6, 2006
Messages
5,640
Heap files are just unordered records. I always like to think of it like a text file of records. Like CSV. A heap file, without a clustered index, means the whole thing must be read to find what you are looking for. In programming terms, it's like reading a text file to make an edit. It's a dumb file structure so you need to read the entire thing into memory, make the change, and write it back out again. No easy one shot slice and dice. Heap files are slow (maybe the record you need is at the very end.. you don't know until you read it all.. so bad big-o).. it's nice that no time is wasted maintaining the clustering in an index.. no cluster index means less space used.

I am not sure what you mean by 'heap based index'.

Clustered indexing has benefits of faster retrieval, faster access, but it takes times keeping the index in check for any insert, update, or deletes. Also there's the extra space for it, and the data is physically stored in order. Occasionally the clustering gets fragmented so you need to reindex to keep up the retrieval and access. I like to think of it like a tree structure or a dictionary.. the order is in it's nature. It helps get you get to your data faster (even if it is at the end).

Nonclustering is typically like clustering except the physical storage of the data doesn't matching the logical ordering. Though as I think about it.. I think MSSQL and the like have gotten away from the physical ordering of data. Non clustering is more like an index or glossary. A map to point you to the ordering.

https://en.wikipedia.org/wiki/Database_storage_structures#Heap_files
http://pages.cs.wisc.edu/~dbbook/openAccess/Minibase/spaceMgr/heap_file.html
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/heaps-tables-without-clustered-indexes?view=sql-server-2017
https://www.mssqltips.com/sqlservertip/1254/clustered-tables-vs-heap-tables/
https://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-clustered-vs-nonclustered-indexes-whats-the-difference/
 
  • Like
Reactions: PTNL
like this
Top