SQL Server Tables

Question

Post reply

SQL Server Tables

Viewing 12 posts - 31 through 42 (of 42 total)

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

Miles Neale SSChampion Points: 13147 More actions · Answer 1

Love the discussion. I got it by process of elimination.

Not all gray hairs are Dinosaurs!

Ken SSCoach Points: 17191 More actions · Answer 2

I really wasn't expecting this much discussion around this question! But thanks for the humor and insights. Makes for enjoyable reading in between "real" work.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

PHYData DBA (8/27/2013)
Nice easy question... I always wondered why you can have a Primary Key on a heap and its still a heap if there is no clustered index on the PK.
Maybe this?
http://en.wikipedia.org/wiki/Heap_(data_structure) :unsure:

No, definitely not. A Heap in Sql-Server is neither of the heap structures described on that page, it has no key-based tree structure. If a heap has a primary key, that primary key is not a cluster key and the index supporting the primary key will be a search tree, which will of course have min heap properties but also satisfies a lot more order conditions; the index does not contain any data other than keys and pointers, so the table heap will have a lot of data which isn't in the min heap, which should make it clear that they are definitely not the same thing. Heap is one of those words with rather a lot of different meanings.

Tom

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 4

L' Eomot Inversé (8/27/2013)
Heap is one of those words with rather a lot of different meanings.

So heap, like orthogonal and obtuse, have many different meanings. Only one of which was covered by today's QOTD.

sqlnaive SSCoach Points: 17437 More actions · Answer 5

SQLRNNR (8/27/2013)

sknox (8/27/2013)
SQLRNNR (8/27/2013)
For me, the wording of the question implies that a requirement of a heap be that it is completely devoid of indexes.
Then you're not thinking in set-based terms.
The question:
"What is a table called, if it does not have either a Clustered or a nonclustered index?"
translates to
SELECT Description
FROM TableTerminology
WHERE HasClusteredIndex = 0
AND HasNonClusteredIndex = 0;
With the dataset:
HasClusteredIndex HasNonClusteredIndex Description
0 0 Heap
0 1 Heap
1 0 Clustered Table
1 1 Clustered Table
the answer is clear.
Haha - got me there :-D.
Funny how re-reading the question after a couple of hours sleep can make the wording of the question less "disagreeable".:cool:

+100

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 6

I like this definition -

If updating a row does not affect other records (though it affects secondary indexes) the table is a heap.

Very short exact and clear.

This might have come from Christopher J. Date but I can not be certain. It is not in the Third Manifesto.

TomThomson SSC Guru Points: 104773 More actions · Answer 7

PHYData DBA (8/27/2013)
I like this definition -
If updating a row does not affect other records (though it affects secondary indexes) the table is a heap.
Very short exact and clear.
This might have come from Christopher J. Date but I can not be certain. It is not in the Third Manifesto.

That's another different meaning for heap, one that I've never come across before.

If updating a row affects other records (other than effects through triggers and effects on index nodes other than the leaf elements of a clustered index if one exists), it's not a row in a table in a relational database because in a relational system updates to rows generally don't affect other rows. So that definition appears to say that every table in a relational database (ignoring the effect of triggers) is a heap. Well, maybe cascading effects through a foreign key constraint may effect other rows, so if you have those in your version or the relational model (some don't, some do; I'm a do) a table which is the target of a foreign key of which it is also the source where updates are cascaded is not a heap, but everything else is.

It's almost the sort of illogical stuff that Date would come up with of course, almost on a par with his strange ideas about NULL (especially his "dialogue" with Codd) and his even stranger claims about his atomicity principle being right while Codd's is wrong; but only almost - I think it's probably too silly even for Date; after all, he isn't stupid. So I think you are wrong when you suggest he's where it came from.

Tom

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 8

PChiragS

SSCarpal Tunnel

Points: 4965

August 28, 2013 at 7:58 am

#1645586

Nice question.. and awesome discussion...

manik_anu SSCrazy Points: 2397 More actions · Answer 9

In which database the heap table will be stored?

nice question...

Manik
You cannot get to the top by sitting on your bottom.

udayroy15 Ten Centuries Points: 1077 More actions · Answer 10

udayroy15

Ten Centuries

Points: 1077

September 7, 2013 at 5:52 am

#1648315

Easy.........

jfgoude SSCrazy Points: 2586 More actions · Answer 11

so easy question

so big headache after reading your answers and comments

Dazli SSC Veteran Points: 235 More actions · Answer 12

Don't think 'nonclustered index' shoud be included in the question.

SQL Server Tables

Cookies on SQLServerCentral