Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Simon_L
Starting Member
32 Posts |
 Posted - 2008-11-05 : 09:31:32
|
| Under what circumstances could you justify/allow a table to be a heap ? a temporary dumping ground that was written to far more than it was read from for example ? Or is there no reason at all why a table shouldn't have at least one index ?thanks ~simon |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-11-05 : 11:28:51
|
| You are wanting to limit this discussion to base tables (not temp tables) I take it?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Simon_L
Starting Member
32 Posts |
Posted - 2008-11-05 : 11:34:07
|
| yes purely just base tables thanks simon |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-05 : 11:37:20
|
| It would be better when Tables needs to have massive DML operation. It would be worst if tables needs to get defragmented. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-11-05 : 11:44:00
|
| Maybe for a logging table? -- something that never gets updated or deleted, only constantly inserted to. Probably with a non clustered index on some sort of date field so you can still retrieve info without scanning the whole table.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-05 : 11:58:45
|
quote:
Originally posted by Transact Charlie
Maybe for a logging table? -- something that never gets updated or deleted, only constantly inserted to.Good point. It always writes to next pages it finds in Heap table Probably with a non clustered index on some sort of date field so you can still retrieve info without scanning the whole table.Non-clustered index on date field will only store date on leaf level but if query contains other columns then it has to scan whole table to find it. Covering index or Nonclustered index with Non-key column will cover everything you need .Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
|
|
|
|
|
|
|
Mitigatng Circumstances for a Heap ?